Steps to monitor the SQL Server Services

By: Jugal Shah   |   Updated: 2013-01-28   |   Comments (21)   |   Related: > 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.

About the author

Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips