SQL Services Status Check - An Evolution Part 2

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

Problem

In my last tip Checking SQL Service Status - An Evolution (part I), I spoke about the important need to be able to monitor and keep track of the status of all necessary SQL Server Services.  I also showed you one method using t-sql, xp_cmdshell and an OS component called SC.exe (service control).  Sometimes, using OS components or even xp_cmdshell is not feasible for all sorts of reasons, including security, or limited DBA access. In this tip we look at other techniques to get the same information.

Solution

Here we can revisit the undocumented, but more generally known, stored procedure call xp_servicecontrol.  First, the caveat.  I wanted to make sure that everyone knows that xp_servicecontrol is an undocumented XP, which means it is not supported and Microsoft can decide to change the code or remove it anytime.  However, it has been a part of SQL Server since version 7.0 through version 2008.  It is actually recommended by MS, as an alternative you use xp_cmdshell 'SC query <servicename>' .  In that case, you can see Part I of my SQL Service Series.  Nonetheless, xp_servicecontrol is a handy one to have in your toolkit.  Despite the warnings, and the caveat, we know that the code is NOT going to change, retroactively, in previous versions.

I will show you how we can use this extended stored procedure to get the status of all the SQL Services.

Method 2 - Using xp_servicecontrol

You can use this extended stored procedure to monitor and query status, as well as control the service by starting and stopping it.  There are two general parameters that take the Action desired, and the Service Name.  Here are the actions and syntax that you can specify:

In this case, we will have to know all of the exact Service Names of each service, which is different from the Display Name.  The Service Name is the parameter that we need to pass to the @ServiceName.  This of course can be found out by going to the Services Management Console in the control panel.  The quickest way to invoke it, is to go to the Start Menu Button --> Run, type "services.msc" and hit <Enter>.

Therefore, for example, we see that the main SQL Server Service Name is "MSSQLServer", and we would query its status using the following syntax:

With the script I will be discussing in this tip, I already have all the service names of the common SQL Server services included for you, so you don't need to look them up.

How the Script Works

This script will determine the status of SQL server service(s) if they are running or stopped, and also determine if the service is installed or not.  This script has been adapted and modified to account for the correct Integration Services service name, depending on whether its SQL Server version 2005, MsDtsServer, or SQL Server version 2008, MsDtsServer100. (The original source can be found here.) We also add some email notification, using DB Mail , so you can set up a scheduled job to run each morning before you get in.

First, the above script will declare and initialize all the variables for each SQL Service being evaluated. Then, it will look at each SQL related service independently. As of SQL Server 2005, not all services are installed by default, and therefore each service that is installed, will have an existing registry key in the Registry.

In order to evaluate the registry keys, to see if they exist, you will need to use the xp_regread undocumented extended stored procedure via T-SQL

This is the syntax of the xp_regread:

EXECUTE xp_regread [@rootkey=]'rootkey',
   [@key=]'key'
   [, [@value_name=]'value_name']
   [, [@value=]@value OUTPUT] 

For example, we can get the DISPLAY NAME for the SQLSERVERAGENT service, which is stored in the registry, by running:

DECLARE @regkeyval varchar(20), @value varchar(255), @rc int
EXEC @rc=master.dbo.xp_regread 
  @rootkey= 'HKEY_LOCAL_MACHINE',
  @key='System\CurrentControlSet\Services\SQLSERVERAGENT',
  @value_name='DisplayName',
  @regkeyval=@value OUTPUT
SELECT @value AS DISPLAY_NAME

All we are doing in the case of this script, is checking for the existence of the key, essentially confirming the service is installed.  If it returns an integer value of '1', then it evaluated as true, and therefore the code will continue on to check the status of the service.  Otherwise, the result will yield the text 'NOT INSTALLED', and will appear in the 'Current Service Status' column in the final output.

In addition to the Service Name and Status, you will also see the Time Checked, Server Name, and Instance Name, as some services are Instance Independent.  In other words, you can have multiple installations of SSIS.  You can also remove the column(s) from the SELECT statement, you don't wish to see displayed.

Finally, here is what the query results should look like:

SQL Server Uptime

Before we finish this tip, along with the need to get the service status of your SQL Server Instance, another important thing to know is when the SQL Server was last restarted.  With that information, you can easily calculate SQL Server Uptime and answer the question how long has the SQL Server been running?  There are some ways to derive this, but the easiest way is to grab the 'login_time' from the very first process/spid that logs in upon startup from the sys.sysprocesses view.  The script below will calculate the difference between the current time and the login time.

--Calculate SQLServer Uptime -Returns [Days:Hours:Minutes:Seconds]
select RTRIM(CONVERT(CHAR(3),DATEDIFF(second,login_time,getdate())/86400)) + ':' +
RIGHT('00'+RTRIM(CONVERT(CHAR(2),DATEDIFF(second,login_time,getdate())%86400/3600)),2) + ':' +
RIGHT('00'+RTRIM(CONVERT(CHAR(2),DATEDIFF(second,login_time,getdate())%86400%3600/60)),2) + ':' +
RIGHT('00'+RTRIM(CONVERT(CHAR(2),DATEDIFF(second,login_time,getdate())%86400%3600%60)),2) AS [DD:HRS:MIN:SEC]
from sys.sysprocesses  --sysprocesses for SQL versions <2000
where spid = 1 

Conclusion

The above method is yet another way to get the current status of your SQL Services.  With this method, we are aiming to use (2) two unsupported and undocumented extended stored procedures - xp_servicecontrol and xp_regread, which we showed you here.  You can see how powerful and potentially malicious these could be, and therefore must be conscious of securing these extended procs. (See some articles on that below).  Furthermore, it seems like quite a hefty query with several lines of code (261 to be exact), just to get us the status of our services.  It would be real neat, if we can return all the relevant service(s) status and start-up time of the SQL Server in one line of code.  Imagine that!  Well, stay tuned for the next and concluding tip of this 3-part series, SQL Service Status - An Evolution, and I will show you how!

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:

• Monitor, Start and Stop SQL Server services using xp_servicecontrol
• Securing Extended Stored Procedures - Revoke EXECUTE rights to PUBLIC
• How do I secure a SQL Server extended stored procedure?
• Send Mail Using sp_send_dbmail
• Setting Up Database Mail for SQL 2005 (works on SQL 2008)

About the author

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