Microsoft is giving more power to the user of Microsoft SQL Server with each release of SQL Server. SQL Server 2012 is no exception and came up with lots of new features, tools, DMVs, system stored procedure, functions and much more. Today we are going to see one of the new DMV in SQL Server 2012, named “Sys.dm_server_services” which returns information about the SQL Server, Full-Text, and SQL Server Agent services in the current instance of SQL Server. You can use this dynamic management view to report status information about these services.
Generally we used to use WMI script or Services.msc under control panel -> Administrative tools to see the installed services of SQL Server and the status of those services. In SQL Server 2012, you can login to SQL Server Management Studio (SSMS), if SQL Server Services is running, and execute simple DMV query to see all SQL Server related services with its status. Isn’t it useful?
Currently I am using WMI script to keep a watch on status of services which used to send me an email if any of the services found stopped in my production server and SQL Server 2012 made my life easy by providing “Sys.dm_server_services”.
Let us execute simple SELECT query on “Sys.dm_server_services”.
SELECT * FROM Sys.dm_server_services
Here is the screen capture of above query:
You can get important information about SQL Server related services like:
- Service Name
- Service Description whether it is configured to run Automatic or manual etc.
- Current status like 1(stopped), 4(running), 7 (Paused) etc.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.