Steps to monitor the SQL Server Services

By: Jugal Shah | Read Comments (19) | Related Tips: More > SQL Server Configurations

Problem

I have a need to monitor the SQL Server Services in my environment. Are there any programmatic options to do so? Check out this tip to learn more.

Solution

To monitor the SQL Server Services I will use the Get-WmiObject cmdlet and win32_service class in PowerShell. The Get-WmiObject cmdlet gets instances of Windows Management Instrumentation (WMI) classes or information about the available classes.

Syntax

Here is the sample syntax:

Get-WmiObject [[-Class] ] [-Authority ] [-List] [-Recurse]
 [-Amended] [-AsJob] [-Authentication {Default | None | Connect | Call | Packet 
| PacketIntegrity | PacketPrivacy | Unchanged}] [-ComputerName ] 
[-Credential ] [-EnableAllPrivileges] 
[-Impersonation {De fault | Anonymous | Identify | Impersonate | Delegate}] [-Locale ] 
[-Namespace ] [-ThrottleLimit ] []

You can get the more information on the Get-WmiObject cmdlet, by executing the commands below on the PowerShell command prompt.

## for detailed information
get-help Get-WmiObject -detailed
## For technical information, type:
get-help Get-WmiObject -full

Before we proceed with the PowerShell script to monitor the SQL Services, I will show you few examples of PowerShell script with some different options. You can practice these sample commands on the PowerShell command prompt.

The command below will list of all the Services on the local computer and its properties.

get-wmiobject -Class win32_service | select-object *

The command below will print a list of all the methods and properties associated with the win32_service class.

Get-WmiObject -Class win32_service | get-member

The command below will print a list of specific properties of the services which are used with the Select-Object clause.

get-wmiobject -Class win32_service | select-object  Name,state,systemname,startmode,startname

The command below will only list out the MSSQLServer (Default Instance) or MSSQL$InstanceName (Named Instance) SQL Services.

get-wmiobject -Class win32_service | where {$_.name -like 'MSSQLServer' -OR $_.name -like 'MSSQL$*' } 
| select-object  Name,state,systemname,startmode,startname

Now I hope you have a pretty clear understanding on how to use the Get-WmiObject cmdlet to get the list of SQL Services and their status. Based on this information, we will follow the steps below to setup SQL Service monitoring using T-SQL.

Step 1 - In this step we will create two tables tbl_SQLCMDB (Windows Server and SQL Server instance to monitor) and tbl_serviceStatus (SQL Service monitoring results).

-- Table to store the windows server name & SQL instance name
CREATE TABLE [dbo].[tbl_SQLCMDB](
 [SQLinstanceName] [varchar](100) NULL,
 [windowsServerName] [varchar](100) NULL
) ON [PRIMARY]
-- Inserting Data into SQLCMDB
insert into tbl_SQLCMDB values ('SQLDBPool','SQLDBPool') -- Default Instance
insert into tbl_SQLCMDB values ('JugalPC','JugalPC\MSSQL')
-- SQL Service Monitoring Output Table
CREATE TABLE [dbo].[tbl_serviceStatus](
 [SQLServiceName] [varchar](500) NULL,
 [servicestatus] [varchar](100) NULL,
 [windowservername] [varchar](500) NULL,
 [startmode] [varchar](100) NULL,
 [UpdateDate] [datetime] NULL DEFAULT GETDATE(),
 [startname] [varchar](1000) NULL,
 [InstanceName] [varchar](1000) NULL
) ON [PRIMARY]

Step 2 - To monitor the SQL Services we are going to use the tbl_SQLCMDB table and the sample PowerShell script below.

This PowerShell command will retrieve all the services with a name containing "SQL".

get-wmiobject -Class win32_service | where {$_.name -like '*SQL*'} 
| select-object  Name,state,systemname,startmode,startname

As an example, we are going to run the above PowerShell script either by using SQL Server Management Studio or a stored procedure, make sure xp_cmdshell is enabled on the SQL Instance. To enable xp_cmdshell you must have at least the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

You can execute the below script to check and enable xp_cmdshell.

declare @chkCMDShell as sql_variant
select @chkCMDShell = value from sys.configurations where name = 'xp_cmdshell'
if @chkCMDShell = 0
begin
 EXEC sp_configure 'xp_cmdshell', 1
 RECONFIGURE;
end
else
begin
 Print 'xp_cmdshell is already enabled'
end

Step 3 - Execute the script below to monitor the SQL Services and their status. You can also create a stored procedure with the T-SQL code below and execute it for monitoring purposes.

set nocount on
-- Variable to store windows server name
DECLARE @server_name varchar(100)
DECLARE @SQLInstance_name varchar(100)
-- table to store PowerShell script output
CREATE TABLE #output
(line varchar(max) null)
-- Declaring cursor to fetch windows server name
DECLARE server_cursor CURSOR FOR 
select distinct LTRIM(rtrim(windowsservername)) as windowsServerName,SQLInstanceName from tbl_sqlcmdb
OPEN server_cursor
FETCH NEXT FROM server_cursor 
INTO @server_name, @SqlInstance_Name
WHILE @@FETCH_STATUS = 0
BEGIN
declare @svrName varchar(255)
declare @sql varchar(400)
set @svrName = @server_name
-- Preparing PowerShell Dynamic Statement
set @sql = 'powershell.exe -c "Get-WmiObject  -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class win32_service |  where {$_.name -like ' + QUOTENAME('*SQL*','''') + '} | select-object  Name,state,systemname,startmode,startname  | for each{$_.name+''|''+$_.state+''%''+$_.systemname+''*''+$_.startmode+''@''+$_.startname+''!''}"'
-- Inserting PowerShell Output to temporary table
insert #output
EXEC xp_cmdshell @sql
-- Deleting the rows which contains error or has not sufficient data
delete from #output  where len(line) < 30
update #output set line = line + '!' where line not like '%!%'
IF (SELECT COUNT(*) FROM #output where line like '%Get-Wmi%') = 0
begin
insert into tbl_serviceStatus(SQLServiceName,servicestatus,windowservername,startmode,startname,InstanceName)
select  rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as SQLServiceName 
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) ))) as ServiceStatus
--,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) ))) as WindowsServerName 
,@server_name
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('*',line)+1, (CHARINDEX('@',line) -1)-CHARINDEX('*',line)) ))) as startmode
,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('@',line)+1, (CHARINDEX('!',line) -1)-CHARINDEX('@',line)) ))) as startname
,@SQLInstance_name
from #output
where line is not null and LEN(line) > 30
end
-- Clearing output table
truncate table #output
-- Next windows record
FETCH NEXT FROM server_cursor 
INTO @server_name,@SQLInstance_name
END 
CLOSE server_cursor;
DEALLOCATE server_cursor;
-- dropping the temporary table
drop table #output

Sample Output

Here is some sample output as a point of reference:

SELECT * FROM dbo.tbl_serviceStatus

Automation

To automate this process, can create a SQL Server Agent Job from the above T-SQL code to monitor the SQL Services in your environment and you can generate different kind of reports by querying the tbl_serviceStatus table.

For example:

List of SQL Services Stopped
List of SQL Services which has start mode Manual
List of SQL Service running under local system account
List of SQL Service AD accounts in the environment

Next Steps

Configure Database Mail on your SQL Server instances and generate alert emails to help improve the automation and notification.
Add additional field to tbl_SQLCMDB table for example ServerType (PRD, QA, UAT or DEV), Version, IsCluster, etc. to help prioritize issues.
Be proactive and begin monitoring the status of your SQL Server services in your environment.

Last Update: 1/28/2013

About the author

Become a paid author

Learn More

Friday, September 19, 2014 - 8:53:53 AM - Kapil Swamy

Read The Tip

Hi,

While running the Step 3 from T-SQL and getting data from remote SQL server, I am encountering below error however when i directly run the below powershell command from command prompt I am getting output with no issues. Please assist

Get-WmiObject -ComputerName 'GAL02059' -Class win32_service | where {$_.name -like '*SQL*'} | Select-Object Name, systemname, StartMode, State, StartName | foreach {$_.Name+ '|' + $_.systemname + '%'+ $_.StartMode + '*' + $_.State + '@' + $_.StartName + '!'}

Get-WmiObject : Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESS

DENIED))

At line:1 char:14

+ Get-WmiObject <<<< -ComputerName 'SUL03750' -Class win32_service | where {

$_.name -like '*SQL*'} | select-object Name,State,systemname,StartMode,StartNa

me | foreach{$_.Name+'|'+$_.State+'%'+$_.systemname+'*'+$_.StartMode+'@'+$_.St

artName+'!'}

+ CategoryInfo : NotSpecified: (:) [Get-WmiObject], UnauthorizedA

ccessException

+ FullyQualifiedErrorId : System.UnauthorizedAccessException,Microsoft.Pow

erShell.Commands.GetWmiObjectCommand

Saturday, November 30, 2013 - 3:33:56 AM - mathi	Read The Tip
how to syn two sql db.. one is local and another in web...

Monday, April 08, 2013 - 5:37:27 PM - Mike Eastland	Read The Tip
With the ability to run sql agent job steps as ActiveX (< 2008) or Powershell (>= 2008), I don't see a need for xp_cmdshell.

Friday, March 22, 2013 - 4:39:57 PM - Simplicity

Read The Tip

First, having SQL Server go out to xp_cmdshell is doing it backwards. Have your powershell/whatever run on its own (Windows Task Scheduler, perhaps) and use sqlcmd to put results into SQL with a limited permission account.

Second, for a simpler method of gathering WMI, at a command prompt, simply use

wmic /node:"YourServerOrIPAddress" service WHERE (Caption LIKE "%SQL%") get /all

Reference:

http://technet.microsoft.com/en-us/library/bb742610.aspx

Thursday, February 07, 2013 - 10:19:06 AM - Chris Page

Read The Tip

Thanks for your article, it is just what I was looking for so that I can monitor service status rather than relying on our service provider, I've started putting together a set of blog posts on my blog talking through what I have done locally to provide a monitoring service based on your powershell script so many thanks for getting me started.

The one significant issue I hit early on was rights - some machines simply refused to let me access get-wmiobject on the remote server inspite of me having rights, it took some support from MSDN to understand the xp_cmdshell runs with the agent rights so was just a case of making sure appropriate rights were on other machines (for production purposes I would suggest a proxy account dedicated to the purpose).

Keep up the good work.

Saturday, February 02, 2013 - 7:47:02 PM - Jose Aguilar

Read The Tip

Hi Jugal, Great article, I would like to share the way I figured it out some time ago,

This is a set of files which run under cmd console, with the sqlcmd from SQL SERVER 2005 SE SP3 in order to use the sqlcmd,
There are 5 steps to follow up until you get your own set of files according with following

1.- Create the file ssservis.sql to review current sql server services status,
you can test it on ssms, bellow you can see commented lines, because those
services are not available in my home sqlserver 2005 express edition, you may test
each of your servers to figure out available services: I use to have all
of the listed services in a production environment with 65 servers, most of them
in sql server 2000 less than 20 with sql server 2005 and sql server 2008

----------------------------------------------------------------------
--TSQL file ssservis.sql to run on each server
----------------------------------------------------------------------
set nocount on
CREATE TABLE #ServicesStatus
(
myid int identity(1,1),
serverName nvarchar(100) default @@serverName,
serviceName varchar(100) NULL,
Status varchar(50) NULL,
checkdatetime datetime default (getdate())
)

DECLARE @IsVersion as smallint
DECLARE @VersSQL as varchar(320)

SET NOCOUNT ON
SELECT @VersSQL = @@VERSION
IF CHARINDEX('SQL Server 2000', @VersSQL) > 0
BEGIN
SELECT @IsVersion = 2000
END
IF CHARINDEX('SQL Server 2005', @VersSQL) > 0
BEGIN
SELECT @IsVersion = 2005
END
IF CHARINDEX('SQL Server 2008', @VersSQL) > 0
BEGIN
SELECT @IsVersion = 2008
END
IF CHARINDEX('SQL Server 2010', @VersSQL) > 0
BEGIN
SELECT @IsVersion = 2010
END
--SELECT @IsVersion, @VersSQL

-- Check status
IF @IsVersion > 2000
BEGIN
  INSERT #ServicesStatus (Status)
  EXEC xp_servicecontrol N'querystate',N'MSSQLSERVER'
  update #ServicesStatus set serviceName = 'MSSQLSERVER' where myid = @@identity

--  INSERT #ServicesStatus (Status)
--  EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT'
--  update #ServicesStatus set serviceName = 'SQLServerAGENT' where myid = @@identity

  INSERT #ServicesStatus (Status)
  EXEC xp_servicecontrol N'querystate',N'MSDTC'
  update #ServicesStatus set serviceName = 'MSDTC' where myid = @@identity

--  INSERT #ServicesStatus (Status)
--  EXEC xp_servicecontrol N'querystate',N'ReportServer' --
--  update #ServicesStatus set serviceName = 'ReportServer' where myid = @@identity

  INSERT #ServicesStatus (Status)
  EXEC xp_servicecontrol N'querystate',N'sqlbrowser'
  update #ServicesStatus set serviceName = 'Browser' where myid = @@identity

  INSERT #ServicesStatus (Status)
  EXEC xp_servicecontrol N'querystate',N'SQLWriter' --
  update #ServicesStatus set serviceName = 'SQLWriter' where myid = @@identity

--  INSERT #ServicesStatus (Status)
--  EXEC xp_servicecontrol N'querystate',N'msftesql' --
--  update #ServicesStatus set serviceName = 'msftesql' where myid = @@identity

--  INSERT #ServicesStatus (Status)
--  EXEC xp_servicecontrol N'querystate',N'MsDtsServer' --
--  update #ServicesStatus set serviceName = 'MsDtsServer' where myid = @@identity

  INSERT #ServicesStatus (Status)
  EXEC xp_servicecontrol N'querystate',N'MSSQLSERVEROLAPSERVICE' ---
  update #ServicesStatus set serviceName = 'MSSQLSERVEROLAPSERVICE' where myid = @@identity
END
ELSE
BEGIN
  INSERT #ServicesStatus (Status)
  EXEC xp_servicecontrol N'querystate',N'MSSQLSERVER'
  update #ServicesStatus set serviceName = 'MSSQLSERVER' where myid = @@identity

  INSERT #ServicesStatus (Status)
  EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT'
  update #ServicesStatus set serviceName = 'SQLServerAGENT' where myid = @@identity

  INSERT #ServicesStatus (Status)
  EXEC xp_servicecontrol N'querystate',N'MSDTC'
  update #ServicesStatus set serviceName = 'MSDTC' where myid = @@identity

  INSERT #ServicesStatus (Status)
  EXEC xp_servicecontrol N'querystate',N'Browser'
  update #ServicesStatus set serviceName = 'Browser' where myid = @@identity
END

SET NOCOUNT ON
SELECT
'SERVICE STATUS'     'APPLICATION',
'jose aguilar'      'REFERENCE FOR COMMENTS',
SUBSTRING(CAST(@@serverName AS NVARCHAR(20)),1,20)   'SERVER',
SUBSTRING(CAST(serviceName AS NVARCHAR(26)),1,26)   'SERVICE',
SUBSTRING(CAST(checkdatetime AS NVARCHAR(20)),1,20)   'TODAY',
SUBSTRING(CAST(Status AS NVARCHAR(20)),1,20)    'STATUS'
FROM #ServicesStatus
SET NOCOUNT OFF
DROP TABLE #ServicesStatus

----------------------------------------------------------------------------------------
--end of file ssservis.sql
----------------------------------------------------------------------------------------

2.- List servers to be tested on file ssservrs.txt as server-instance|user|psw ; in the producion environment
I used to list servers by their IP instead of names:IP|user|psw; in this case I am using names, but I feel it
is better with IPs.

GALAXY\SQLTWOK|JoseAguilar|_pa$$w0rd_
GALAXY\SQLEXPRESS|JoseAguilar|_pa$$w0rd_

3.- Define a sqlcmd batch file:ssservis.bat, to go on each server with a FOR - DO MSDOS command as bellow

@ECHO OFF
FOR /F "eol=; tokens=1,2,3 delims=| " %%i in (.\ssservrs.txt) DO (
sqlcmd -S %%i -U %%j -P %%k -i %1 -w4096 -h-1 < exit.txt
)

4.- Define an exit file:exit.txt for each iteration, ONLY ONE SINGLE LINE

quit

5.- Define a file:services.bat to start iterations applying a the query from step 1 as showed

@echo off
echo "GET SQL SERVER SERVICES RESULT"
ssservis.bat ssservis.sql > result.txt
echo END.

6.- Review results on file:result.txt which should be like:

SERVICE STATUS jose aguilar GALAXY\SQLTWOK    MSSQLSERVER                Jan 30 2013 2:59PM Running.
SERVICE STATUS jose aguilar GALAXY\SQLTWOK   SQLServerAGENT             Jan 30 2013 2:59PM Running.
SERVICE STATUS jose aguilar GALAXY\SQLTWOK   MSDTC                      Jan 30 2013 2:59PM Running.
SERVICE STATUS jose aguilar GALAXY\SQLTWOK    Browser                    Jan 30 2013 2:59PM Running.
SERVICE STATUS jose aguilar GALAXY\SQLEXPRESS   MSSQLSERVER                Jan 30 2013 2:59PM Running.
SERVICE STATUS jose aguilar GALAXY\SQLEXPRESS   MSDTC                      Jan 30 2013 2:59PM Running.
SERVICE STATUS jose aguilar GALAXY\SQLEXPRESS   Browser                    Jan 30 2013 2:59PM Running.
SERVICE STATUS jose aguilar GALAXY\SQLEXPRESS   SQLWriter                  Jan 30 2013 2:59PM Stopped.
SERVICE STATUS jose aguilar GALAXY\SQLEXPRESS   MSSQLSERVEROLAPSERVICE     Jan 30 2013 2:59PM Running.

7.- Optionally you can append headers at the top of the results file, since I have suppressed them from de sqlcmd command as

APPLICATION REFERENCE FC SERVER SERVICE TODAY STATUS
----------------------------------------------------------------------------------------------------------

so file results.txt reads:
APPLICATION    REFERENCE FC SERVER  SERVICE       TODAY   STATUS
----------------------------------------------------------------------------------------------------------
SERVICE STATUS jose aguilar GALAXY\SQLTWOK    MSSQLSERVER                Jan 30 2013 2:59PM Running.
SERVICE STATUS jose aguilar GALAXY\SQLTWOK   SQLServerAGENT             Jan 30 2013 2:59PM Running.
SERVICE STATUS jose aguilar GALAXY\SQLTWOK   MSDTC                      Jan 30 2013 2:59PM Running.
SERVICE STATUS jose aguilar GALAXY\SQLTWOK    Browser                    Jan 30 2013 2:59PM Running.
SERVICE STATUS jose aguilar GALAXY\SQLEXPRESS   MSSQLSERVER                Jan 30 2013 2:59PM Running.
SERVICE STATUS jose aguilar GALAXY\SQLEXPRESS   MSDTC                      Jan 30 2013 2:59PM Running.
SERVICE STATUS jose aguilar GALAXY\SQLEXPRESS   Browser                    Jan 30 2013 2:59PM Running.
SERVICE STATUS jose aguilar GALAXY\SQLEXPRESS   SQLWriter                  Jan 30 2013 2:59PM Stopped.
SERVICE STATUS jose aguilar GALAXY\SQLEXPRESS   MSSQLSERVEROLAPSERVICE     Jan 30 2013 2:59PM Running.

8.- Finally and not less important: when ever a server is not available, there will be an error message in the results.txt file,
in between the other server results, you must figure out from your servers list wich is down, last column of results shows current
status for the service, this is very useful to test many servers in a couple of seconds, I use to test 65 production servers, and
eventually start up sqlagent when it was down for any reason, in order to make sure that overnight backup jobs runs properly.

9.- ONE LAST THING, VERY IMPORTANT, THIS CAN BE USED TO APPLY A QUERY ON MANY SERVERS IN A MASSIVE WAY, SO BE CAREFULL PLEASE, I USED TO APPLY MASSIVE CHANGES FOR ALL SQL SERVERS FROM EACH BRANCH IN A COUPLE OF SECONDS, SO BE AWARE AND BE CAREFUL, I ALSO USED TO REQUEST FROM DEVELOPERS BEFORE SUBMITTING ANY DOCUMENTED RFC TO PLACE SQL SRIPT STATMENTs USING PRINT 'BEFORE CHANGE', 'PRINT WHILE CHANGING'AND 'PRINT AFTER CHANGE' TO HAVE RFC RESULTS DOCUMENTED.

I HOPE YOU ENJOY IT, AND REMEMBER STEPS 1-5 ARE IN SEPARATED FILES AND EXECUTION STARTS WITH THE FILE services.bat IN THE CMD CONSOLE FROM THE FOLDER WHERE YOU PLACED ALL OF THE 5 FILES.

BEST REGARDS

Jose Aguilar

Thursday, January 31, 2013 - 9:55:57 AM - PRADEEPT SINGH	Read The Tip
All the services are in running mode except MSSQLServerADHelper100.

Thursday, January 31, 2013 - 8:33:56 AM - Jugal

Read The Tip

Pradeep, Copy below command & execute it from the PS command prompt and let me know the result

get-wmiobject -Class win32_service | where {$_.name -like '*SQL*'} | select-object Name,state,systemname,startmode,startname

Thursday, January 31, 2013 - 12:54:09 AM - PRADEEPT SINGH

Read The Tip

while executing this code in POWER SHELL Getting error message ----

"An empty pipe element is not allowed.
At line:1 char:2
+ | <<<    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordEx
   ception
    + FullyQualifiedErrorId : EmptyPipeElement"

Please suggest now what to do.

Wednesday, January 30, 2013 - 8:31:33 AM - Jugal

Read The Tip

It is already mentioned in the article that "Before we proceed with the PowerShell script to monitor the SQL Services, I will show you few examples of PowerShell script with some different options."

You can execute the queries/script from Step 1 onwards in SSMS.

Wednesday, January 30, 2013 - 4:05:06 AM - PRADEEPT SINGH

Read The Tip

Ok I am executing it on SSMS as you have mentioned above in Point number 2.

"As an example, we are going to run the above PowerShell script either by using SQL Server Management Studio or a stored procedure, make sure xp_cmdshell is enabled on the SQL Instance. To enable xp_cmdshell you must have at least the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles"

Wednesday, January 30, 2013 - 4:04:27 AM - PRADEEPT SINGH

Read The Tip

Getting error message - in Power shell

"An empty pipe element is not allowed.
At line:1 char:2
+ | <<<< select-object Name,state,systemname,startmode,startname
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordEx
   ception
    + FullyQualifiedErrorId : EmptyPipeElement"

Tuesday, January 29, 2013 - 10:50:03 AM - Jugal	Read The Tip
You have to execute that script on Power Shell command prompt. Let me know what error you are getting while execution.

Tuesday, January 29, 2013 - 6:30:38 AM - PRADEEPT SINGH

Read The Tip

Hi Dear,

I am not able to execute the above code i.e. step 2

get-wmiobject -Class win32_service | where {$_.name -like '*SQL*'}
| select-object Name,state,systemname,startmode,startname

Monday, January 28, 2013 - 5:53:45 PM - alfredo

Read The Tip

FOR EL SCRIPT, THE CORRECT IS ...

| for each{$ BAD

| foreach{$ OK

THANK YOU

SANTIAGO, CHILE

Monday, January 28, 2013 - 10:56:52 AM - Jugal	Read The Tip
Darshan, I would suggest you to create central monitoring server using the mentioned steps in the article. Monitor all the SQL Instances from one location.

Monday, January 28, 2013 - 10:38:55 AM - Ed - sqlscripter

Read The Tip

Great article! I saw a article from Microsoft where they compared the WSH\VB Script way to the powershell coded version. Much less code with PS. Here is how I did this way back the old way with WSH\VB\ASP scripting. I demo'd this this back then at a PASS July LIVE Web Presenter and a MSDN Code Camp in Malvern PA.

'Edward J Pochinski III I made many mods to this code after it came from MS
'sqlscripters@hotmail.com 06/14/2004
'Change the file extension to .vbs to call
'MS used a DSN and I did not even test that method and used a DNS_LESS/OLEDB connection.
'Create ActiveX connection object
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
'perfmon is the database with the ServicesTable you will need to edit Source= UID= and PWD=
objConn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=ServerNameYouWantToQuery;UID=loginId;PWD=Password;DATABASE=perfMon "

objRS.CursorLocation = 3
objRS.Open "SELECT * FROM Services_Table" , objConn, 3, 3
'This denotes local machine you can use a remote server inside the quotes
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colRetrievedEvents = objWMIService.ExecQuery _
    ("Select * from Win32_Service where name = 'SQLSERVERAGENT'",,48)
For Each objEvent in colRetrievedEvents
    objRS.AddNew
    objRS("Name") = objEvent.Name
    objRS("ExitCode") = objEvent.ExitCode
    objRS("Started") = objEvent.Started
    objRS("State") = objEvent.State
    objRS("Status") = objEvent.Status
    objRS("SystemName") = objEvent.SystemName
    objRS.Update
Next

objRS.Close
objConn.Close

--Then to alarm on the data

--This code calls the above WMI and checks the sql table

Create Procedure sp_dba_ServicesMonitor AS

--This procedure will check the services table and page a
--dba if the SQL or Agent services are down.
--Implementation is as follows: The SQL Agent Job step should call this procedure

--Load the services data from the remote server
DECLARE @retval int
DECLARE @cmdstr nvarchar(4000)
--Check the path to the vbs file
SET @cmdstr = ' D:\ScriptingAdsi\sqldba_scripts\Services_all_Dump_To1Table.vbs' --Set path to match your machine

EXEC @retval = master..xp_cmdshell @cmdstr
IF (@retval = 0) --0 is a successful return code meaning successful
     BEGIN
print '**************************************************************'
print '/***** The file was called successfully'
print '**************************************************************'
print ''
     END
else
     Begin
Print '**********************************************************************************************'
print ' A problem has occurred call someone who cares'
Print '**********************************************************************************************'
/*
Select @srvname = @@servername
select @fr = '*'
select @t = 'pin@skytel.com,pin@archwireless.net'
select @sub = @srvname +' Alert Error'
select @bod = 'A problem occurred executing the vbs file'
exec sp_dbaSendMail1 @fr , @t , @sub, @bod
*/
     END

--Part 2 : Check the status of the services and note the qualified path to the table
Declare @sysname varchar(55), @Service_name varchar(55)
select @sysname = SystemName, @service_name = name from perfmon..Services_Table where state = 'stopped'
IF @@rowcount <> 0
Begin

Print @sysname + ' ' + @service_name + ' service is not running call the Blue Eyes White Dragon....'
/*
Declare @msg varchar(255),@fr varchar(50),@t varchar (255),@sub varchar(100), @bod varchar(255)
--Build the string to send out as a page
select @msg = @sysname + ' ' + @service_name + ' service is not running'
select @fr = 'email@somwone.com'
select @t = 'pin@skytel.com,pin@archwireless.net' --edit who this is going to as 1111111@skytel.com
select @sub = 'Alert'
select @bod = @msg
exec sp_dbaSendMail1 @fr , @t , @sub, @bod
*/
--Note the qualifiers
truncate table perfmon..Services_Table

END
ELSE
Begin
--Note the qualifiers
truncate table perfmon..Services_Table
Print ' We are at the return Section of code'
Return
END

Monday, January 28, 2013 - 9:29:41 AM - Rohit Garg

Read The Tip

Good article.

Rohit

http://mssqlfun.com/

Monday, January 28, 2013 - 7:35:45 AM - Darshan

Read The Tip

Hi Jugal,

One question here, If we schedule the windows shcedule task to know status of sql agent service every 15-30 minutes. Does it affect CPU & memory usage? On my local server I feel it. Can't we set up WMI event or same thing like it ,which give instant alert,no need to execute it in some interval. I tried this but somehow I'm not getting alert. If I use same logic using powershell it works.

http://sqlish.com/alert-when-sql-server-agent-service-stops-or-fails/

Sponsor Information

	Follow Get Free SQL Tips Twitter LinkedIn Google+ Facebook Pinterest RSS	Learning DBAs Developers BI Professionals Careers Q and A Today's Tip	Resources Tutorials Webcasts Whitepapers Tools	Search Tip Categories Search By TipID Top Ten Authors	Community First Timer? Pictures Free T-shirt Contribute Events User Groups Author of the Year	More Info Join About Copyright Privacy Disclaimer Feedback Advertise
Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved Some names and products listed are the registered trademarks of their respective owners.

Steps to monitor the SQL Server Services

Problem

Solution

Syntax

Sample Output

Automation

Next Steps

About the author

Post a comment or let the author know this tip helped you.

Follow

Learning

Resources

Search

Community

More Info