Differences

This shows you the differences between two versions of the page.

Link to this comparison view

monitors:bbsql-server [2010/09/14 19:11] (current)
Line 1: Line 1:
 +====== monitor script for Microsoft SQL server 2000 and up ======
 +
 +^ Author | [[ bart.gillis@cegeka.be| Bart Gillis]] |
 +^ Compatibility | Xymon 4.2 |
 +^ Requirements | BBWin 0.12  |
 +^ Download | None |
 +^ Last Update | 2010-03-25|
 +
 +===== Description =====
 + This is a port of the bbsql-server script that was written 03/2002 by Jason Benassi (jbenassi at futurecasting.com)
 +Used for monitoring MS SQL Server ​
 +  * Blocking Locks -> This test will look at a 20 minute window for database jobs
 +  * Statistics on "​Latest Backup date"
 +  * Statistics on the Size and usage of the transaction logs
 +
 +===== Installation =====
 +=== Client side ===
 +  * Install BBWin 0.12 on the MSSQL server
 +  * Place this script anywhere on your SQL-Server 7, 2000, 2005 or 2008 server.
 +  * Configure the BBWin client to execute this script as an external script.
 +  * Alternatively you can configure BBWin in central mode and schedule this script in the Windows "​Scheduled Tasks" scheduler. ​
 +=== Server side ===
 +(none)
 +==== bbsql-server.vbs ====
 +<hidden onHidden="​Show Code ?" onVisible="​Hide Code ?">
 +<code vb>
 +' Script for Xymon (Big Brother) Monitoring Tool
 +' Modified 02/2010 By Bart Gillis
 +' email: Bart.Gillis@cegeka.be
 +' This is a port of the bbsql-server script that was written 03/2002 by Jason Benassi
 +' ​          ​jbenassi@futurecasting.com
 +' Used for monitoring SQL Server Blocking Locks
 +' And database jobs
 +' This test will look at a 20 minute window for database jobs
 +' ​
 +' ​          Feel free to modify this script (just send me the updates so I can use them) :)
 +
 +' Modification History:
 +' bbsql-server-v2.0
 +' ​          - Changed the SQL connection string. Authentication to a trusted server -> No password information required in this script.
 +' ​          - 32 bit/64 bit processor architecture check.
 +
 +' bbsql-server-v3.0
 +' ​          - Check the latest finished Database Backup date and time
 +' ​          - Check Log Size and Log space used of the transaction log.
 +
 +' bbsql-server-v3.0.1
 +' ​          - Minor big fix around line 122 (Thank you Richard Finegold)
 +' ​            the script only opened the file if it exists, then deletes immediately after
 +' ​            ​reading instead of after the variable assignment.
 +' ​          - changed <td> to <th> in table headers (Thank you Richard Finegold)
 +
 +' ​          ​Install Instructions:​
 +' ​          Place this script anywhere on your SQL-Server 7, 2000, 2005 or 2008 server.
 +' ​          ​Configure the BBWin client to execute this script as an external scrip.
 +' ​          ​Alternatively you can configure BBWin in central mode and schedule this script in the Windows "​Scheduled Tasks" scheduler. ​
 +' ​          Next configure the tesname variables below.
 +
 +Dim version, ws, extPath, testName, testString, gbFailed, outputfilename,​ tempfilename,​ Proc
 +
 +'​************* Configure these parameters ***********************
 +
 +testName = "​mssql"​ 'This is what the bb test will be called
 +version = "​V3.0.1"​
 +
 +'​***************************************************************
 +outputfilename = replace(testName,"​.",""​)
 +tempfilename = outputfilename & "​.tmp"​
 +Const adStateClosed = 0
 +
 +Set ws = WScript.CreateObject("​WScript.Shell"​)
 +ProcessorCheck
 +            if Proc = "​x86"​ then
 +' ​           extPath = "​c:​\"​
 +           ​extPath = ws.RegRead("​HKEY_LOCAL_MACHINE\SOFTWARE\BBWin\tmppath"​) '​Registry key that stores the ext script path on 32bit Architecture
 +            WScript.Echo Proc
 +            else
 +' ​           extPath = "​c:​\"  ​
 +           ​extPath = ws.RegRead("​HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\BBWin\tmppath"​) '​Registry key that stores the ext script path on 64bit Architecture
 +            WScript.Echo Proc
 +            end if
 +gbFailed = false
 +CheckJobStats extPath
 +CheckLocks
 +CheckBackup
 +CheckTransactionlog
 +WriteFile extPath
 +
 +Sub WriteFile (strFilePath)
 +    Set fso = CreateObject("​Scripting.FileSystemObject"​)
 +                        If gbFailed = False Then
 +                                   ​testString = "green " & Date & " " & Time & vbcrlf & testString & vbcrlf
 +                        Else
 +                                   ​testString = "red " & Date & " " & Time & vbcrlf & testString & vbcrlf ​      
 +                        End If
 +            Set f = fso.OpenTextFile(strFilePath & "​\"​ & outputfilename , 8 , TRUE)
 +                        f.Write testString
 +                        f.Close
 +            Set fso = Nothing
 +End Sub
 +
 +Sub CheckLocks ​
 +            Dim cn, rs, sqlStr, strResult, htmlcolorcode
 +    Set cn = CreateObject ("​ADODB.Connection"​)
 +            cn.Open "​Provider=SQLOLEDB;​Data Source="​ & strServer & ";​Initial Catalog=master"​ & ";​Integrated Security=SSPI"​
 +            Set rs = CreateObject ("​ADODB.Recordset"​)
 +            htmlcolorcode="##​00ff00"​
 +                        ​
 +            sqlStr = _
 +                        "​Select DISTINCT CONVERT (smallint, l1.req_spid) AS spid, " & _
 +                        "​Left(db_name(l1.rsc_dbid),​ 10) AS dbName, " & _
 +                        "​Left(object_name(l1.rsc_objid),​ 20) AS ObjName, " & _
 +                        "​l1.rsc_indid AS IndId, " & _
 +                        "​substring (v.name, 1, 4) AS Type, " & _
 +                        "​substring (l1.rsc_text,​ 1, 16) AS Resource, " & _
 +                        "​substring (u.name, 1, 8) AS Mode, " & _
 +                        "​substring (x.name, 1, 5) AS Status " & _
 +                        "FROM master.dbo.syslockinfo l1, " & _
 +                        "​master.dbo.syslockinfo l2, " & _
 +                        "​master.dbo.spt_values v, " & _
 +                        "​master.dbo.spt_values x, " & _
 +                        "​master.dbo.spt_values u " & _
 +                        "WHERE l1.rsc_type = v.number " & _
 +                        "AND v.type = '​LR'​ " & _
 +                        "AND l1.req_status = x.number " & _
 +                        "AND x.type = '​LS'​ " & _
 +                        "AND l1.req_mode + 1 = u.number " & _
 +                        "AND u.type = '​L'​ " & _
 +                        "AND l1.rsc_type <>2 /* NOT a DB lock */ " & _
 +                        "AND l1.rsc_dbid = l2.rsc_dbid " & _
 +                        "AND l1.rsc_bin = l2.rsc_bin " & _
 +                        "AND l1.rsc_objid = l2.rsc_objid " & _ 
 +                        "AND l1.rsc_indid = l2.rsc_indid " & _ 
 +                        "AND l1.req_spid <> l2.req_spid " & _
 +                        "AND l1.req_status <> l2.req_status " & _
 +                        "ORDER BY substring (l1.rsc_text,​ 1, 16), substring (x.name, 1, 5) "
 +            rs.Open sqlStr, cn , 3 ,3 
 +
 +        If Not rs.EOF Then
 +            ​
 +            Do While Not rs.EOF
 +                    htmlcolorcode="#​ff0000"​
 +            gbFailed = True
 +            strResult = strResult & " ​           Block Found -- Details: " & vbcrlf & _
 +                " ​                                             SPID= " & rs.Fields("​spid"​) & _
 +                " ​                                             DBNAME= " & rs.Fields("​dbName"​) & _
 +                " ​                                             OJBNAME= " & rs.Fields("​ObjName"​) & _
 +                " ​                                             INDID = " & rs.Fields("​IndId"​) & _
 +                " ​                                             TYPE = " & rs.Fields("​Type"​) & _
 +                " ​                                             RESOURCE = " & rs.Fields("​Resource"​) & _
 +                " ​                                             MODE = " & rs.Fields("​Mode"​) & _
 +                " ​                                             STATUS = " & rs.Fields("​Status"​) & vbcrlf & vbcrlf
 +
 +                wscript.echo "​Blocking Lock SPID = " & rs.Fields("​spid"​)
 +            rs.MoveNext
 +            Loop
 +        Else
 +            strResult = strResult & vbcrlf & " ​             No Blocking Locks."​ & vbcrlf
 +                                   ​wscript.echo "No Blocking Locks"
 +                        End If
 +            testString = testString & "<​br><​b>​ <​font color="​ & htmlcolorcode & ">​-------------------- SQL SERVER BLOCKING LOCKS -------------------</​font></​b><​br><​br>"​
 +            testString = testString & strResult
 +                                                           
 +            Set rs = Nothing ​                                  
 +            Set cn = Nothing
 +            ​
 +End Sub
 +
 +Sub CheckJobStats (strFilePath)
 +            Dim cn, rs, cmd, sqlStr, fso, f, htmlStr1, htmlStr2, rColor, strStatus, tmpStr, bFailed, htmlcolorcode ​
 +    Set cn = CreateObject("​ADODB.Connection"​)
 +            cn.Open "​Provider=SQLOLEDB;​Data Source="​ & strServer & ";​Initial Catalog=msdb"​ & ";​Integrated Security=SSPI"​
 +
 +            sqlStr = "​DECLARE all_jobs_cursor CURSOR " & _
 +                    "FOR " & _
 +                    "​SELECT job_id FROM sysjobhistory GROUP BY job_id " & _
 +                    "OPEN all_jobs_cursor " & _
 +                    "​DECLARE @current_id uniqueidentifier " & _
 +                    "FETCH NEXT FROM all_jobs_cursor " & _
 +                    "INTO @current_id " & _
 +                    "WHILE @@FETCH_STATUS = 0 " & _
 +                    "BEGIN " & _
 +                    "​SELECT s.*, v.name FROM sysjobhistory s, sysjobs_view v " & _
 +                    "WHERE s.job_id = v.job_id and s.job_id =  @current_id " & _
 +                    "and cast(cast(run_date as varchar)+'​ '​+cast(run_time/​10000 as varchar)+':'​+cast(run_time/​100%100 as varchar)+':'​+cast(run_time%100 as varchar) as datetime)>​getdate()-1.0/​24"​ & _
 +                    "ORDER BY instance_id " & _
 +                    "FETCH NEXT FROM all_jobs_cursor " & _
 +                    "INTO @current_id " & _
 +                    "END " & _
 +                    "CLOSE all_jobs_cursor " & _
 +                    "​DEALLOCATE all_jobs_cursor"​
 +    Set rs = cn.Execute(sqlStr)
 +
 +        bFailed = False
 +        strStatus = "​green"​
 +            htmlcolorcode= "#​00ff00"​
 +
 +            Set fso = CreateObject("​Scripting.FileSystemObject"​)
 +            Do Until rs Is Nothing
 +                        if NOT rs.State = adStateClosed then
 +                        Set f = fso.OpenTextFile(strFilePath & "​\"​ & tempfilename , 8, True )
 +            rCnt = 1
 +    If Not rs.EOF Then
 +        Do While Not rs.EOF
 +        bFailed = False
 + If rs.Fields("​run_status"​) <> 1 Then
 + strStatus = "​red"​
 + End If
 +
 + If rs.Fields("​run_status"​) = 0 Then
 + bFailed = True
 + rColor = "&​red"​
 + htmlcolorcode = "#​ff0000"​
 +            Else
 +                bFailed = False
 +                rColor = "&​green"​
 +                htmlcolorcode = "#​00ff00"​
 +            End If
 +
 +            If Not rCnt > 1 Then       
 +                f.Write " ​           Job Name: <​b>"​ & rs.Fields("​name"​) & "</​b>"​ & vbcrlf
 +            End If
 + msg = replace(rs.Fields("​message"​),​vbCRLF,​vbCRLF & " ​            "​)
 +        str =     " ​                     Step ID: " & rs.Fields("​step_id"​) & " ​  Step Name: " & rs.Fields("​step_name"​) &  _
 +                  " ​  Run Duration: " & rs.Fields("​run_duration"​) & " ​  Run Status: " & rs.Fields("​run_status"​) &  _
 +                  " ​  ​Retries Attempted: " & rs.Fields("​retries_attempted"​) & " ​  Run Date: " & rs.Fields("​run_date"​) & " ​           Run Time: " & rs.Fields("​run_time"​) & vbcrlf & _
 +                  " ​                     Message: " & vbcrlf & " ​            "​ & msg & vbcrlf & _
 +                  " ​                     -----------------------------------------------------------------------------------------------------------------"​ & vbcrlf
 +        f.Write str
 +        wscript.echo str
 +
 +        rCnt = rCnt + 1
 +        rs.MoveNext
 +    Loop
 +        f.Write vbcrlf & vbcrlf
 +        End If
 +        If bFailed = True Then
 +          gbFailed = True
 +        End If                                       
 +    f.Close  ​
 +    rCnt = 1
 +    End If
 +Set rs = rs.NextRecordSet
 +            ​
 +Loop
 +if fso.FileExists(strFilePath & "​\"​ & tempfilename) then
 +       Set f = fso.OpenTextFile(strFilePath & "​\"​ & tempfilename ,1 )
 +               If Not f.AtEndOfLine Then
 +                       ​tmpStr = f.ReadAll
 +               Else
 +                       ​tmpStr = " ​             No Job History. " & vbcrlf
 +               End If
 +       ​f.Close
 +       ​fso.DeleteFile strFilePath & "​\"​ & tempfilename
 +else
 +       ​tmpStr = " ​             No Job History. " & vbcrlf
 +end if
 +
 +            tmpStr = "<​br>​Version:"​ & version & "<​br><​b>​ <​font color="​ & htmlcolorcode & ">​----------------------- SQL SERVER JOBS --------------------------</​font></​b><​br><​br>"​ & tmpStr
 +
 +            testString = testString & tmpStr & vbcrlf & vbcrlf
 +
 +            Set fso = Nothing
 +            Set cn = Nothing
 +            Set rs = Nothing
 +End Sub
 +            ​
 +Sub ProcessorCheck ()
 +Dim  WshShell, WshSysEnv
 +
 +Set WshShell = WScript.CreateObject("​WScript.Shell"​)
 +Set WshSysEnv = WshShell.Environment("​SYSTEM"​)
 +Proc = WshSysEnv("​PROCESSOR_ARCHITECTURE"​)
 +End Sub
 +
 +Sub CheckBackup ()
 +Dim cn, rs, sqlStr, strStatus, htmlcolorcode ,strResult
 +    Set cn = CreateObject ("​ADODB.Connection"​)
 +            cn.Open "​Provider=SQLOLEDB;​Data Source="​ & strServer & ";​Initial Catalog=master"​ & ";​Integrated Security=SSPI"​
 +            Set rs = CreateObject ("​ADODB.Recordset"​)
 +            htmlcolorcode = "#​00ff00"​
 +            strResult = vbcrlf & "<​table border=1 cellpadding=2 cellspacing=2><​tbody><​tr><​th>"​ &_
 +                                               "​DBASE NAME" & vbTab & "</​th><​th>"​ & vbTab &_
 +                                               "​BACKUP FINISHED" ​ & "</​th></​tr>"​ & vbcrlf
 +            sqlStr = _
 +                        "​SELECT db.name, ISNULL(MAX(backup_finish_date),​ '​1900-01-01'​) as backup_finish_date " & _
 +                        "FROM master.dbo.sysdatabases db " & _
 +                        "LEFT JOIN msdb..backupset bu ON ( db.name = bu.database_name ) " & _
 +                        "WHERE db.name != '​tempdb'"​ &_
 +                        "group by db.name"​
 +        Set rs = cn.Execute(sqlStr)
 +' ​                      ​rs.Open sqlStr, cn , 3 ,3
 +                        Do While Not rs.EOF
 +                    strResult = strResult &_
 +                                                                       "<​td>"​ & rs.Fields("​name"​) & vbTab & "</​td><​td>"​ & vbTab &_
 +                                               ​rs.Fields("​backup_finish_date"​) & "</​td></​tr>"​ & vbcrlf
 +                    rs.MoveNext
 +                        Loop
 +                        strResult = strResult & "</​tbody></​table>"​
 +            testString = testString & vbcrlf & "<​b> ​       <font color="​ & htmlcolorcode & ">​------------------- SQL SERVER LAST BACKUP DATES -----------------</​font></​b>"​
 +            Wscript.Echo strStatus & strResult
 +            testString = testString & strResult
 +End Sub
 +
 +Sub CheckTransactionlog ()
 +Dim cn, rs, sqlStr, strStatus, htmlcolorcode ,strResult
 +    Set cn = CreateObject ("​ADODB.Connection"​)
 +            cn.Open "​Provider=SQLOLEDB;​Data Source="​ & strServer & ";​Initial Catalog=master"​ & ";​Integrated Security=SSPI"​
 +            Set rs = CreateObject ("​ADODB.Recordset"​)
 +            htmlcolorcode = "#​00ff00"​
 +            strResult = vbcrlf & "<​table border=1 cellpadding=2 cellspacing=2><​tbody><​tr><​th>"​ &_
 +                                               "​DBASE NAME" & vbTab & "</​th><​th>"​ & vbTab &_
 +                                               "​TRANSACTION LOG Size (MB)" ​ & vbTab & "</​th><​th>"​ & vbTab &_
 +                                               "​TRANSACTION LOG Usage (%)" ​ & "</​th></​tr>"​ & vbcrlf
 +            sqlStr = _
 +                        "DBCC SQLPERF (logspace)"​
 +        Set rs = cn.Execute(sqlStr)
 +' ​                      ​rs.Open sqlStr, cn , 3 ,3
 +                        Do While Not rs.EOF
 +                    strResult = strResult &_
 +                                                                       "<​td>"​ & rs.Fields("​Database Name") & vbTab & "</​td><​td>"​ & vbTab &_
 +                                               ​rs.Fields("​Log Size (MB)") & vbTab & "</​td><​td>"​ & vbTab &_
 +                                               ​rs.Fields("​Log Space Used (%)"​)&​ "</​td></​tr>"​ & vbcrlf
 +                    rs.MoveNext
 +                        Loop
 +                        strResult = strResult & "</​tbody></​table>"​
 +            testString = testString & vbcrlf & "<​b> ​       <font color="​ & htmlcolorcode & ">​----------------- SQL SERVER TRANSACTION LOG INFO ----------------</​font></​b>"​
 +            Wscript.Echo strStatus & strResult
 +            testString = testString & strResult
 +End Sub
 +</​code>​
 +</​hidden>​
 +
 +===== Known  Bugs and Issues =====
 +
 +===== To Do =====
 +I still have to implement some alerting on
 +  * the latest finished Database Backup date and time check
 +  * “Log Size” and “Log space used” of the transaction log check
 +
 +===== Credits =====
 +
 +===== Changelog =====
 +
 +  * **2003-11-24 **
 +    * Initial release: Downloaded from deadcat.net
 +
 +  * **2006-05-20** bbsql-server-v2.0
 +    * Changed the SQL connection string. Authentication to a trusted server -> No password information required in this script.
 +    * 32 bit/64 bit processor architecture check.
 +
 +  * **2010-03-25** ​ bbsql-server-v3.0
 +    * Check the latest finished Database Backup date and time
 +    * Check "Log Size" and "Log space used" of the transaction log.
 +  * **2010-04-16** bbsql-server-v3.0.1
 +    * Minor bug fix around line 122 (Thank you Richard Finegold) the script only opened the file if it exists, then deletes immediately after reading instead of after the variable assignment.
 +    * changed <td> to <th> in table headers (Thank you Richard Finegold)
 +
  
  • monitors/bbsql-server.txt
  • Last modified: 2010/09/14 19:11
  • (external edit)