Checking SQL Services Status - An Evolution Part 1

By:   |   Comments (15)   |   Related: 1 | 2 | 3 | 4 | More > Database Administration


As a production DBA, one of the key things that must proactively be checked on a daily basis is the status of the SQL Server instances, and all of its related SQL Server Services.  In editions of SQL Server 2000 and earlier, there were only two main services for concern, MSSQLSERVER and SQLSERVERAGENT.  In SQL Server 2005 and higher, there are several new services to consider, as well as some of them being instance independent.  This means there could be a multitude of services on a single server, which must be sorted out of all the other existing windows services. 

The individual services we need to monitor are:

  • MS SQL Server Service
  • SQL Server Agent Service
  • SQL Browser Service - Instance Independent
  • Integration Services - Instance Independent
  • Reporting Services
  • Analysis Services
  • Full Text Search Service

Through the versions of SQL Server, us DBA's had to do some t-sql acrobatics in order to create a script to gather all the relevant services, and return its status.  I will present the various scripts and methods here, (specifically for SQL 2005, 2008/R2, and 2012), and the pros and cons of each.  You will be happy to know, it is indeed an evolution from version to version, culminating in a neat new DMV for SQL Server 2012.  I will discuss different methods in this first of a three-part series.

Method 1 - Using xp_cmdshell and sc.exe

You may already know that SQL Server 2005 out of the box is "secure by default."  Therefore, this method requires that 'xp_cmdshell' is enabled on each server, in order to query the services.  As a quick public service message, xp_cmdshell should only be enabled when absolutely needed, and is best practice to leave it disabled to reduce the surface area of attack.

Also, you'll need to be on Windows XP/Server 2003 or higher, to use sc.exe.   SC.exe retrieves and sets control information about services - you can find more information here on Technet or more specific SC query, which obtains and displays information about a specified service (or driver).

We will be using xp_cmdshell and sc.exe for this first exercise.  There is a world of potential with these components, such as automation, batch scripts, etc., but we will define its use in the SQL Server world.  For folks still using SQL 2000/Windows 2000, you can get the equivalent .exe from the NT Resource Kit called NETSVC.EXE - but it is not installed by default. If you make few adjustments to my script, you can use it for SQL2K. You would typically install it in the system32 directory, so you can call it from the command line (cmd). Here is some further information on NETSVC, and a comparative on using SC.exe and Netsvc.exe to control services.

So, to enable xp_cmdshell on you SQL Server, you need to run the following:

sp_configure 'Show Advanced Options',1
go sp_configure 'xp_cmdshell',1
go reconfigure

Now, the only way to make xp_cmdshell to play nice in SQL Server is to get creative with the T-SQL Exec command, using INSERT.. EXEC xp_cmdshell... You can click on the highlighted link for the MSDN article.  The script below shows the various ways to insert results of a stored procedure into a table, passes values persisted in temporary tables, uses a cursor to iterate through the list of services, and finally, eliminate NULL values that are created with xp_cmdshell.  You can see it is not the most elegant code, but nonetheless works as intended.

How the Script Works

The way it works is that we build our list of SQL Services, and then we query each one's current status.

First, a temporary table is created to hold all the SQL Services we will discover, using SC.

Then, we will use the above INSERT..EXEC xp_cmdshell, and have SC query do two searches, one for each insert. One is for all the services with "SQL" in its name, and the second one searches for "MsDts", which is the service name for Integration Services (SSIS).  You will notice I use a cmd and parameter "find /V "string".  You can specifiy this multiple times separated with the "|" pipe symbol to eliminate these strings from the search.  I specifically eliminate the "DISPLAY_NAME", so it returns the actual "SERVICE_NAME" that we will use to enumerate the service status.

Next, we delete all the NULLs from the table, and add an identity column, that we will use later to join the two tables.

We build our cursor to iterate through the list of SQL Server services, and run the 'sc query ' + @nSvcName + '|find "STATE.  What this does is get the current status, and only returns the line output which gives us the "STATE" of the service.  The various states can be:

  • 1 - STOPPED
  • 4 - RUNNING

The unformatted cmd output looks like this:

windows system 32 sc query mssqlserver

As it iterates through the list of SQL Services, it inserts the status output, where the line references "STATE", into the #hold_sql_status temporary table, and deletes the extra NULL.  At the end, it will then add an Identity column to this table as well.  Now that we have both tables populated in the same order, we can create a query that joins them together on the identity column.  Here is the final complete script:

CREATE TABLE tempdb.dbo.hold_sql_services
SQL_ServiceName VARCHAR(100)
INSERT INTO tempdb.dbo.hold_sql_services EXEC xp_cmdshell 'sc query type= service state= all |find "SQL" |find /V "DISPLAY_NAME" |find /V "AD" | find /V "Writer"'

INSERT INTO tempdb.dbo.hold_sql_services EXEC xp_cmdshell 'sc query type= service state= all |find "MsDts" |find /V "DISPLAY_NAME"'

DELETE tempdb.dbo.hold_sql_services WHERE SQL_ServiceName IS NULL

ALTER TABLE tempdb.dbo.hold_sql_services

CREATE TABLE tempdb.dbo.hold_sql_status
SvcStatus VARCHAR(55)

@cmd VARCHAR(500)
@SvcStatTxt VARCHAR(100)

SET @gService = CURSOR FOR
FROM tempdb.dbo.hold_sql_services

OPEN @gService

FROM @gService INTO @oSvcName

@nSvcName=REPLACE(@oSvcName,'SERVICE_NAME: ','')
--Print @nSvcName

SET @cmd='sc query ' + @nSvcName + '|find "STATE"'
--Print @cmd

INSERT INTO tempdb.dbo.hold_sql_status EXEC xp_cmdshell @cmd

DELETE tempdb.dbo.hold_sql_status WHERE SvcStatus IS NULL

FROM @gService INTO @oSvcName


ALTER TABLE tempdb.dbo.hold_sql_status


FROM tempdb.dbo.hold_sql_status ss
INNER JOIN tempdb.dbo.hold_sql_services sn ON sn.SNID=ss.SSID

/* select @cSTOP=COUNT(*) from tempdb.dbo.hold_sql_status
where SvcStatus like '%STOPPED'
If @cSTOP > 0  -- You can uncomment this block if you want to add logic to email only for STOPPED services -
  BEGIN  */

EXEC msdb.dbo.sp_send_dbmail --@profile_name='SQLAdmin',
@recipients='[email protected]',
@subject='SQL Service(s) Status Update',
@body='This is the latest SQL Server Service(s) Status Report. Please review and take appropriate action if necessary',
@query='select RTRIM(sn.SQL_ServiceName) AS SQL_ServiceName,
       WHEN CHARINDEX(''STOPPED'',ss.SvcStatus) > 0 THEN ''STOPPED''
       ELSE ''UNKNOWN'' END AS ServiceStatus
from tempdb.dbo.hold_sql_status ss
inner join tempdb.dbo.hold_sql_services sn on sn.SNID= ss.SSID'

--END --uncomment this if you uncomment the BEGIN..END block above

DROP TABLE tempdb.dbo.hold_sql_services

The results should look similar to this:

sql server services status output


The above method is one way to get the current status of your SQL Services.  As you can see, there are a lot of caveats and moving parts here.  There must be an easier and cleaner way.  Indeed, we can continue on the evolution of gathering service status, and next we will talk about an alternative solution, method 2, in Part II of this three part series.

Next Steps

In the meanwhile, to learn some more about using the above-referenced components, check out some of these previous tips and resources below:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Robert Pearl Robert Pearl is a SQL MVP, and President of Pearl Knowledge Solutions, Inc., offering SQLCentric monitoring and DBA services.

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

View all my tips

Comments For This Article

Thursday, January 28, 2016 - 2:09:44 AM - Jeff Moden Back To Top (40521)

Well done, Robert.  Thanks for taking the time to build and post this article.

As another public service ;-), a clarification is in order...

Leaving xp_CmdShell disabled may have been labeled as a "best practice" by many but it does absolutely nothing to reduce the surface area of attack other than providing a 3 milliisecond speedbump that attack software is already programmed to overcome.  The reason why is that, unless you've made the extreme andd terrible mistake of allowing non-system admin people (Developers, Users, software logins, etc) the privs to run it directly either by proxy or granting the CONTROL SERVER privs, no one can use xp_CmdShell except those with sysadmin privs... and those are the same people that can turn it on or off.  No one else can use it.  And you've just got to know that an attacker isn't going to try to break in as a login with less than sysadmin privs.  ;-)

If folks really want to protect their machines, turn xp_CmdShell on and then allow that unreasonable visceral fear (although totally unfounded) to finally drive them to do what they really need to do... protect their boxes by following the real best practices of limiting the privs of the SQL Server Login, the SQL Server Agent, forcing good password policy with forced changes through Active Directory on a regular basis, and other true security measures.

If you really want to be afraid, look at the clear text login and password in the code that "Ed" posted in the disucussion.  How is it that people aren't concerned with something like that?

And hat's off to Marc for the SQLCLR suggestion for WMI.  Done correctly, that can be very secure... Still, turning off xp_CmdShell is like putting a veil over rotting meat... it makes people feel better but the flies are still going to get to it. ;-)

Heh... want to really reduce your surface area?  Get rid of SSIS and start doing things with xp_CmdShell and correctly written stored procedures.


Wednesday, February 15, 2012 - 12:59:09 PM - Jason Back To Top (16029)
Since Windows Services is part of OS, why don't you use Powershell easier instead?

Monday, February 13, 2012 - 10:08:43 AM - Robert Pearl Back To Top (15999)

PS SQL Server 2008 R2 SP1 AND SQL 2012...meant to say..

Monday, February 13, 2012 - 10:07:47 AM - Robert Pearl Back To Top (15998)

DMV's are the way to go for Service Status, only if the end-user is on SQL Server 2008 R2 SP1, otherwise they'll need to use alternative methods as described in the article, and some of the comments....

Part II will demonstrate another method via TSQL.

Thursday, February 9, 2012 - 10:17:09 PM - Ed Back To Top (15982)

The DMV's are the way to go, I have developed a huge toolkit with them and MS keeps building on them, over 200 in SQL2005 and over 400 in SQL2008.

Thursday, February 9, 2012 - 7:20:28 PM - Marc Jellinek Back To Top (15981)

Another thing I'd be interested in getting more info on:  The WMI Connection Manager and the WMI Data Reader in SQL Server Integration Services.  This would be a "native" way to get any information made available by WMI.  No scripting necessary.

Thursday, February 9, 2012 - 6:34:39 PM - Robert Pearl Back To Top (15980)

Stay tuned for the rest of the series - I may even mention that :-D

Thursday, February 9, 2012 - 4:19:17 PM - Luke Campbell Back To Top (15976)

If your running SQL Server 2008 R2 with SP1 check out the sys.dm_server_services dmv.

Thursday, February 9, 2012 - 11:27:38 AM - Ed Back To Top (15961)

MS has introduced WMI in the SQL Alerts and you can tap right into the name space on SQL2005 and above, to perform a call across the network from one machine and watch many a simple vb script wrapper works with some surrounding objects it needs such as the table to insert, a handy dandy COM script wrapped in a procedure. There are many ways to perform this now, I demo'd this for my SQL PASS Public lecture years ago: You use a simple T-SQL select and evaluate for the condition from the table inserted.

'Edward J Pochinski III I made many mods to this code after it came from MS
'Change the file extension to .vbs to call
'MS used a DSN and I did not even test that but used a DNS_LESS/OLEDB connection.
'This means this is a commented line of code FYI....
'Create ActiveX connection & objects
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open  "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;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
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("Name") = objEvent.Name
    objRS("ExitCode") = objEvent.ExitCode
    objRS("Started") = objEvent.Started
    objRS("State") = objEvent.State
    objRS("Status") = objEvent.Status
    objRS("SystemName") = objEvent.SystemName


'New Block for additional service to query

Thursday, February 9, 2012 - 10:57:47 AM - Robert Pearl Back To Top (15960)

Of course, if you want to go outside of native sql server, and spend hundreds/thousands of dollars on a monitoring platform, anyone is welcome to do that.  If you have SCOM in-house, as I do, you know that unless you have a MS enterprise wide license or a very deep budget, its is not the most affordable solution for smaller shops.  In addition, SCOM is great, but you also must be schooled in its complex configuration as well.

BTW, the decision to purchase a monitoring tool may be warranted, but when there's a way to leverage a script native/out-of-the-box, that is a preference.  HTH :-)

Thursday, February 9, 2012 - 10:45:18 AM - Number2 Back To Top (15959)

Wouldn't it be better to just use SCOM to monitor SQL instances?

Thursday, February 9, 2012 - 10:41:47 AM - Marc Jellinek Back To Top (15958)

Cool, I look forward to watching the solution ( and the series ) evolve

Thursday, February 9, 2012 - 10:22:05 AM - Robert Pearl Back To Top (15957)

Hi,  Thanks for the comments!  There are certainly a number of ways to query and get the service status, including, but not covered in this series, Powershell.

The article is intended for production-oriented DBAs, wherein CLR may be out of scope, but nonetheless a viable option.  The series keeps it to using T-SQL (which of course is NOT always the most efficient way), but in the end, MS provides us a DMV to use for this purpose, and ultimately the point of "Evolution" in this series...  :-)

Thursday, February 9, 2012 - 10:12:44 AM - Marc Jellinek Back To Top (15956)

Have you considered querying service status through WMI?

You could gather the data from a table-typed CLR stored proc, then insert into a table. This wouldn't even necessarily have to run on the database server you are monitoring, it could all happen on a centralized management/monitoring server (so long as the target server is configured to allow remote WMI calls and the security context the script runs under has the appropriate rights).

It's not terribly hard to do... and MUCH more secure than using xp_cmdshell. Error handling is also much more robust.

Thursday, February 9, 2012 - 8:43:08 AM - Ed Back To Top (15954)

The WIN32 API and WMI makes querying services as easy as pie, you can check the state, status ect....

get free sql tips
agree to terms