Seven Monitoring Scripts

  • Comments posted to this topic are about the content posted at

  • Joseph's tips are always excellent but practicle as well (I would also recommend his book SQL 2000 Fast Answers)

    paul warwick

    paul warwick

  • Just an addendum to my article - for the

    "INSERT #Errors EXEC xp_readerrorlog", this extended proc is in the master database... So change it to:

    INSERT #Errors EXEC master..xp_readerrorlog

    Also - remember to drop the temp table.

    DROP TABLE #Errors


  • Very nice scripts. Need some modifications for me, but these are handy to have around.

    Steve Jones

  • Thank you for all

    but the coolest one is the SQL log script


  • For the script that is looking for jobs still running I think an easyer script will be

    msdb.dbo.sp_get_composite_job_info @execution_status = 1

    since the rest of parameters has a default NULL.


  • This is a great article. My only suggestion would be to use master..xp_readerrorlog in the script in tip # 7.



  • Just what I was looking for to drive my ASP status report.

    One other tip for #7, I modified the SELECT statement to be like this,

    CONVERT(datetime, LEFT(vchMsg, 23), 121) as MsgDate
    , SUBSTRING(vchMsg, 24, 10) as MsgSource
    , SUBSTRING(vchMsg, 33, 800) as MsgText
    FROM #Tmp
    WHERE vchMsg NOT LIKE '%Log backed up%'
    AND vchMsg NOT LIKE '%.TRN%'
    AND vchMsg NOT LIKE '%Database backed up%'
    AND vchMsg NOT LIKE '%.BAK%'
    AND vchMsg NOT LIKE '%Run the RECONFIGURE%'
    AND vchMsg NOT LIKE '%Copyright (c)%'
    AND LTRIM(RTRIM(LEFT(vchMsg, 1))) <> CHAR(9)
    AND ID = 0
    CONVERT(datetime, LEFT(vchMsg, 23), 121) DESC

    Now it shows the date, source and message text in seperate fields. The TOP 50 and ORDER BY I put in so that only the last 50 messages are displayed.

    I'd like to improve it further by using the either the OPENROWSET or OPENQUERY statements to execute the xp_readerrorlog procedure. This would get rid of the temp table and I could put this in a stored procedure on one server and dynamically retrieve the error logs from servers that aren't linked.

    The alternative to the stored procedure approach would be to open a database connection on the ASP page and dynamically build and execute the SQL statement there. Not my preferred option.

    Hope this helps

    Phill Carter


    Colt 45 - the original point and click interface

    Edited by - phillcart on 09/14/2003 8:02:15 PM

    Edited by - phillcart on 09/14/2003 8:03:56 PM

    Colt 45 - the original point and click interface

  • Good stuff.  To para #3 I would add "and unscheduled jobs." 

    I've been burned by someone else either removing or disabling the schedule of a regularly-scheduled job.  At first glance you say "it's still enabled, why didn't it run?"  There's no easy way to check if the schedule was deleted, but it is easy to see if the schedule is disabled.

  • I am going to add some of these into my daily reports.  And yes, I do have co-workers that slip in new sysadmin users!  At least this way I'll know it right away.

    Any good scripts for monitoring replication status?



  • Great scripts.  Very useful. 




  • Excellent.

  • Smart and simple.

    I also find using sysjobs_view useful in place of sysjobs. Especially if you are managing more than one server.

    The extra colum originating_Server can tell which server the job failed on.

  • These scripts are very useful.


  • sql monitoring tips are excelent. i expect more tips like that.

    thax and regards


    sql dba

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply