Get List of Stopped SQL Services from central server with WMI. As a DBA we have to monitor many things. To monitor all SQL Server Serives wotking properly works fine we have to check status of services periodically. We can do that it manually or automatically, Manually - By using Services.msc, Automatically - By using scripting and programming. I have created a WMI Script which is very helpfull to DBA Monitoring Task. Benifits of using WMI. 1) WMI gives detail information without affecting performance. 2) WMI is applicable to handle from central server, you can get information from all remote server without any overhead. For this you just need to create Wnidows user which have WMI Permission. For Permission please see http://nirav.extreme-advice.com/2013/05/15/get-disk-space-usage-from-central-server-with-wmi/ 3) Automatically sends mail for which service is stopped. 4) WMI script has a provision to start stopped services automatically. Get List of Stopped SQL Services from central server with WMI. Step1 : Open notepad and save below code with "list of stopped services.vbs"Dim strComputer Dim objWMIService, objItem, colItems Dim strDriveType,txt,flg Dim arrylist(4) arrylist(0) = ("Server1") arrylist(1) = ("Server2") arrylist(2) = ("Server3") arrylist(3) = ("Server4") arrylist(4) = ("Server5") txt = "<table cellspacing='0' cellpadding='0' border='0' bordercolor='black' id='shell' colspace ='2'>" txt = txt & "<tr bgcolor='#DDDDDD' height=30>" txt = txt & "<th width='50' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Server</th>" txt = txt & "<th width='100' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Service</th>" txt = txt & "<th width='50' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Status</th>" txt = txt & "</tr>" For Each strComputer in arrylist On Error Resume Next Set objWMIService = Nothing Set colItems = Nothing Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2") Set colItems = objWMIService.ExecQuery("Select * from Win32_Service Where DisplayName like'%sql%' and AcceptStop = False") If Err.Number = 0 Then For Each objItem in colItems txt = txt & "<tr Bordercolor='#000000'>" txt = txt & "<td align = 'Left' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& strComputer &"</Font></td>" txt = txt & "<td align = 'Left' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& objItem.DisplayName &"</Font></td>" txt = txt & "<td align = 'Left' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& objItem.State &"</Font></td>" txt = txt & "</tr>" Set objWMIService = Nothing Set colItems = Nothing Next End If Next txt = txt & "</table>" Set objMessage = CreateObject("CDO.Message") objMessage.Subject = "List of stopped sql services" objMessage.From = "Alerts@Sender.com" objMessage.To = "niravgajjar@Receiver.com" objMessage.htmlbody = txt objMessage.SendStep2 : Create one Schedule task and call this VBS file periodically like Daily ones or twice as need of process. You will get mail like below.
Server | Service | Status |
---|---|---|
Server1 | SQL Active Directory Helper Service | Stopped |
Server2 | SQL Active Directory Helper Service | Stopped |
Server2 | SQL Server Agent (MSSQLSERVER) | Stopped |
Server3 | SQL Active Directory Helper Service | Stopped |
Server3 | SQL Server Agent (SQL2K8R2EXP) | Stopped |
Server4 | SQL Server Distributed Replay Client | Stopped |
Server4 | SQL Server Distributed Replay Controller | Stopped |
Server4 | SQL Server Agent (SQL2K12EXPR) | Stopped |
Server5 | SQL Server FullText Search (MSSQLSERVER) | Stopped |
Server5 | SQL Server Active Directory Helper | Stopped |
Step3 : This is an additional step to make changes in script. if you need all stopped services then change query.Select * from Win32_Service Where AcceptStop = Falseif you need all SQL services then change query.
Select * from Win32_Service Where DisplayName like'%sql%'if you need all services.
Select * from Win32_Service