By: Robert Pearl | Comments (3) | Related: 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.
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.
- SQL Server 2012 MSDN documentation on sys.dm_server_services
- SQL Server 2008 R2 MSDN documentation on sys.dm_server_services
- This previous tip talks about the other useful new DMV's in SQL Server.
- MSDN documentation on sys.dm_server_registry and sys.dm_server_memory_dumps
- Review Part I and Part II
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
