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

 

SQL Services Status Check - An Evolution Part 3


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

Problem

In this series Part I and Part II we discussed how important it is to monitor and keep track of the status of all related SQL Server Services.  So far, I demonstrated two methods, one using t-sql, xp_cmdshell and an OS component called SC.exe Part I.  The other solution was to use the undocumented xp called xp_service_control.  I also included a query to return the uptime of the SQL Server engine in Part II.

In each of those above-mentioned methods, you will notice that they require a lot of moving parts, and a lot of lines of t-sql code.  For something so simple as the status of a SQL related service, you would think the Microsoft SQL Server Team, would give us a native way to find out.  Well, the year is 2012, and SQL Server has introduced with its imminent 2012 release, some new DMVs (Dynamic Management Views).

Solution

Method 3 - Using sys.dm_server_services

One of the new DMVs available to us in SQL Server 2012 will expose the status of the SQL Server services. It will also return a whole host of important information relating to the services, including: Service Name, Startup Type, Status, Last Startup Time, Service Account, File Name, Is_Clustered, and the Cluster NodeName.  Everything we need to know about the service, is now available by querying sys.dm_server_services

What many folks do not know about the sys.dm_server_services DMV, is that it was slipped in as part of SQL Server 2008 R2 SP1 (only in service pack 1).  So, yes, if you are using SQL Server 2008 R2, you can install service pack 1, and the new DMVs will be available.  Please note that as of R2 SP1, only information about the SQL Server and SQL Server Agent services in the current instance of SQL Server will be returned.

However, in SQL Server 2012, according to the MSDN documentation, it currently will only return information about the SQL Server, Full-Text, and SQL Server Agent services in the current instance of SQL Server.  You will need to resort to the other methods discussed in Part I & II for additional services.

Nonetheless this is an evolution, and a handy little DMV query will let users know dynamically all the things we needed to manually check by going to the services.msc in the Control Panel.  (My own opinion is that eventually they will add more of the services here).  The sys.dm_server_services is worth discussing, in that it takes one line of code to return all the information we sought to report on with methods discussed in Part I and II. 

Here with this new SQL Service DMV, we can view, whether the service is Running, Stopped, Paused, etc. (there are seven possible status results), whether the service startup is Automatic, Manual, or Disabled, which account is being used for the service, the physical path and filename of where the service executable is installed, is the service installed as a resource of a clustered server, and the name of the cluster node on which the service is installed.

How the Script Works

All one needs to do here, is open up a New Query window, and run the following select statement.

select * from sys.dm_server_services

After the above query is run, you will see the below output.  As you can see, it has eleven columns of data.

sys.dm_server_services output

Furthermore, with all SQL Server DMVs, VIEW SERVER STATE permission, at a minimum, is required. (As sysadmin, the rights are implicit)

GRANT VIEW SERVER STATE TO UserLogin

Whereas the previous xp_servicecontrol method in this series, needed to evaluate the registry to find out the physical installation path, and whether or not the service is installed, the sys.dm_server_services view returns data only on services that are currently installed.

SQL Server Uptime

Finally, we can easily calculate SQL Server Uptime for how long any of the SQL Services have been running. (values not NULL).  Whereas we formerly used the "login_time" column from the sys.sysprocesses view, where spid=1, now we can easily use the "last_startup_time" information of a service from this one DMV.  However, it is important to be aware, that the "last_startup_time" column is stored as a new datatype datetimeoffset(7).  Therefore, in order to calculate the difference between the current time (getdate()) and the last startup time, we must convert the last_startup_time column to a datetime.  This script is shown below, and will return the Service Name, Status, and Service Uptime.

--Calculate SQLServer Uptime -Returns [Days:Hours:Minutes:Seconds] 
select servicename, status_desc, 
RTRIM(CONVERT(CHAR(17),DATEDIFF(second,convert(datetime,last_startup_time),getdate())/86400)) + ':' + 
RIGHT('00'+RTRIM(CONVERT(CHAR(7),DATEDIFF(second,convert(datetime,last_startup_time),getdate())%86400/3600)),2) + ':' + 
RIGHT('00'+RTRIM(CONVERT(CHAR(7),DATEDIFF(second,convert(datetime,last_startup_time),getdate())%86400%3600/60)),2) + ':' + 
RIGHT('00'+RTRIM(CONVERT(CHAR(7),DATEDIFF(second,convert(datetime,last_startup_time),getdate())%86400%3600%60)),2) AS [Service_Uptime D:H:M:S]
from sys.dm_server_services 

Note

I just wanted to point out that many on-line resources have indicated that this is a new DMV specifically for SQL Server 2012 and this is wrong, as I mentioned it is available in SQL Server 2008 SP1.  Moreover, some blogs incorrectly state that it returns status for ALL services.

Conclusion

I hope you enjoyed this series, and found all these methods useful for getting important status information about our SQL Server services, including uptime.  With these three how-to tips, you should be able to use at least one or more of them for all versions of SQL Server.  It is great that Microsoft has realized this is important enough information for the DBA, to have created the sys.dm_server_services dynamic management view for us to use.

Next Steps

Check out some of these previous tips and resources below.



Last Updated: 2012-03-01


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.



    



Friday, December 30, 2016 - 9:59:49 AM - Mark Solomon Back To Top

Nice DM table, and sets of articles with very good content. But good luck checking the SQL Server service itself if the SQL Server Service itself is not running! That's the primary service I'm checking for.

 

 


Tuesday, July 03, 2012 - 3:15:32 PM - - George Back To Top

Very cool.  Now I can monitor the services on my servers and get notified if a service is down.  I have been looking for a way to do this for some time.  I also enjoyed the fact that you presented us with several options.  We run both SQL 2000, SQL 2005, and SQL 2008 but most likely will not have SQL 2012 for quite some time.  <B(

Thanks!!!


Wednesday, March 14, 2012 - 10:16:01 AM - MatthewD Back To Top

How many articles\ways are you going to show this?  How many parts are there going to be in this article?

-M


Learn more about SQL Server tools