Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Checking SQL Services Status - An Evolution Part 1


By:   |   Last Updated: 2012-02-09   |   Comments (15)   |   Related Tips: 1 | 2 | 3 | 4 | More > Database Administration

Problem

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
Solution

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
reconfigure
go sp_configure 'xp_cmdshell',1
go reconfigure
go

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
  • 2 - START_PENDING
  • 3 - STOP_PENDING
  • 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
   
ADD SNID INT IDENTITY(1,1)

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

DECLARE @SvcStatus VARCHAR(100)
DECLARE @nSvcName VARCHAR(100)
DECLARE @oSvcName VARCHAR(100)
DECLARE @gService CURSOR
DECLARE
@cmd VARCHAR(500)
DECLARE @cSTOP INT
DECLARE
@SvcStatTxt VARCHAR(100)

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

OPEN @gService

FETCH NEXT
FROM @gService INTO @oSvcName

WHILE @@FETCH_STATUS = 0
BEGIN
   SET
@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

  
FETCH NEXT
  
FROM @gService INTO @oSvcName
END

CLOSE
@gService
DEALLOCATE @gService

ALTER TABLE tempdb.dbo.hold_sql_status
   
ADD SSID INT IDENTITY(1,1)

SET NOCOUNT ON

SELECT
sn.SQL_ServiceName,
CASE WHEN CHARINDEX('RUNNING',ss.SvcStatus) > 0 THEN 'RUNNING'
      
WHEN CHARINDEX('STOPPED',ss.SvcStatus) > 0 THEN 'STOPPED'
      
WHEN CHARINDEX('START_PENDING',ss.SvcStatus) > 0 THEN 'START PENDING'
      
WHEN CHARINDEX('STOP_PENDING',ss.SvcStatus) > 0 THEN 'STOP PENDING'
      
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

/* 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 [email protected]_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,
  CASE WHEN CHARINDEX(''RUNNING'',ss.SvcStatus) > 0 THEN ''RUNNING''
       WHEN CHARINDEX(''STOPPED'',ss.SvcStatus) > 0 THEN ''STOPPED''
       WHEN CHARINDEX(''START_PENDING'',ss.SvcStatus) > 0 THEN ''START PENDING''
       WHEN CHARINDEX(''STOP_PENDING'',ss.SvcStatus) > 0 THEN ''STOP PENDING''
       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

Conclusion

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:



Last Updated: 2012-02-09


next webcast button


next tip button



About the author




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

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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
Since Windows Services is part of OS, why don't you use Powershell easier instead? http://powershell.com/cs/blogs/ebook/archive/2009/04/10/chapter-17-processes-services-event-logs.aspx#listing-services http://dbace.us

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

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

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 09, 2012 - 10:17:09 PM - Ed Back To Top

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 09, 2012 - 7:20:28 PM - Marc Jellinek Back To Top

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 09, 2012 - 6:34:39 PM - Robert Pearl Back To Top

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


Thursday, February 09, 2012 - 4:19:17 PM - Luke Campbell Back To Top

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


Thursday, February 09, 2012 - 11:27:38 AM - Ed Back To Top

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.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

'New Block for additional service to query


Thursday, February 09, 2012 - 10:57:47 AM - Robert Pearl Back To Top

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 09, 2012 - 10:45:18 AM - Number2 Back To Top

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


Thursday, February 09, 2012 - 10:41:47 AM - Marc Jellinek Back To Top

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


Thursday, February 09, 2012 - 10:22:05 AM - Robert Pearl Back To Top

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 09, 2012 - 10:12:44 AM - Marc Jellinek Back To Top

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 09, 2012 - 8:43:08 AM - Ed Back To Top

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


Learn more about SQL Server tools