Monitor Service Status

Jonathan Kehayias, October 28, 2008

Since the SQL Server Agent depends on the SQL Service to run, creating all monitoring inside of the SQL Agent creates a single point of failure. Further if the SQL Agent fails to start, no jobs created in SQL Agent will run which defeats the entire purpose of automating monitoring of a SQL Server. To ensure that the necessary services are running, a VBScript that uses the Windows Management Instrumentation interface can be used to check the current status of all critical services on a server or list of servers. The service information can be output to a text file which can then be sent to an Administrator by email as an attachment to provide notification of failed, or stopped services that are critical to business operation.

CheckService_SingleEmailAttachment.vbs Script

 
on error resume next
Set iFSO = CreateObject("Scripting.FilesyStemObject")
Set oFSO = CreateObject("Scripting.FilesyStemObject")
 
'Note: change the email ID to your email ID
NotificationEmail="""SQL Monitor"" <SQLMonitor@domain.com>"
SMTPServer = "mail.domain.com"
 
InputFile="C:\Automated DBA\VBScript\Servicelist.txt"
Outputfile="C:\Automated DBA\VBScript\ServiceStatus.txt"
 
Set ifile = iFSO.OpenTextFile(inputfile)
Set ofile = ofso.createTextFile(OutputFile, True)
ofile.writeline "Check Server Service Status Started"
ofile.writeline Now()
ofile.writeline ""
 
Do until ifile.AtEndOfLine
	servicelist= ifile.ReadLine
	strcomputer=left(servicelist,instr(servicelist,",")-1)
	Service =right(servicelist,len(servicelist)-instr(servicelist,","))
 
	Set objWMIService =nothing
	Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
 
	if err.number<>0 then
		ofile.writeline "ServerName: " & strcomputer
		ofile.writeline "ServiceName: " & Service
		ofile.writeline "Error: " & err.number
		ofile.writeline "Error: " & err.description
		ofile.writeline ""	
	else
 
		Set colItems = nothing
		query=""
		query = "Select * from Win32_Service where name = '" & Service & "'"
 
		Set colItems = objWMIService.ExecQuery(query)
			if err.number<>0 then
				ofile.writeline "ServerName: " & strcomputer
				ofile.writeline "ServiceName: " & Service
				ofile.writeline "Error: " & err.number
				ofile.writeline "Error: " & err.description
				ofile.writeline ""
			else
				
				For Each objItem in colItems
					if objItem.State <> "Running" then
					ofile.writeline "ServerName: " & strcomputer
					ofile.writeline "ServiceName: " & Service
					ofile.writeline "Status: " & objItem.Status
					ofile.writeline "State: " & objItem.State
					ofile.writeline ""
					end if
				next
			end if
	end if
 
Loop
 
ofile.writeline "Check Server Service Status Completed"
ofile.writeline Now()
 
ofile.Close
ofile = Nothing
 
Set objMessage = CreateObject("CDO.Message") 
	objMessage.Subject = "SQL Server Service Status Report" 
	objMessage.From = NoitificationEmail
	objMessage.To = NotificationEmail
	objMessage.TextBody = "SQL Server Service Status Report Attached"
	objMessage.AddAttachment(Outputfile)
 
	'The line below shows how to send a webpage from a remote site 
		objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
	'Name or IP of Remote SMTP Server 
		objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
	'Server port (typically 25) 
		objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 
 
	objMessage.Configuration.Fields.Update 
	objMessage.Send
   

Return to Top

ServiceList.txt Sample

 
ServerName1,ServiceName1
ServerName1,ServiceName2
ServerName2,ServiceName1
ServerName2,ServiceName2
 

Return to Top


This is article is part of a series on Automating Common DBA tasks in SQL Server. To see the full series see: Automating Common DBA Tasks

___________________________________________________________________________________________________________________
Page Created By: Jonathan Kehayias, Oct 28, 2008