====== 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 ==== ' 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 to 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 & "
-------------------- SQL SERVER BLOCKING LOCKS -------------------

" 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: " & rs.Fields("name") & "" & 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 = "
Version:" & version & "
----------------------- SQL SERVER JOBS --------------------------

" & 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 & "" & 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 &_ "" & vbcrlf rs.MoveNext Loop strResult = strResult & "
" &_ "DBASE NAME" & vbTab & "" & vbTab &_ "BACKUP FINISHED" & "
" & rs.Fields("name") & vbTab & "" & vbTab &_ rs.Fields("backup_finish_date") & "
" testString = testString & vbcrlf & " ------------------- SQL SERVER LAST BACKUP DATES -----------------" 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 & "" & vbcrlf sqlStr = _ "DBCC SQLPERF (logspace)" Set rs = cn.Execute(sqlStr) ' rs.Open sqlStr, cn , 3 ,3 Do While Not rs.EOF strResult = strResult &_ "" & vbcrlf rs.MoveNext Loop strResult = strResult & "
" &_ "DBASE NAME" & vbTab & "" & vbTab &_ "TRANSACTION LOG Size (MB)" & vbTab & "" & vbTab &_ "TRANSACTION LOG Usage (%)" & "
" & rs.Fields("Database Name") & vbTab & "" & vbTab &_ rs.Fields("Log Size (MB)") & vbTab & "" & vbTab &_ rs.Fields("Log Space Used (%)")& "
" testString = testString & vbcrlf & " ----------------- SQL SERVER TRANSACTION LOG INFO ----------------" Wscript.Echo strStatus & strResult testString = testString & strResult End Sub ===== 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 to in table headers (Thank you Richard Finegold)