Saturday, April 28, 2012

Retrieve the status of SQL Server services programatically

One of the cool features that's got introduced from SQL 2008 R2 is ability of getting the running status of various SQL Server services like SQL Server, Full-Text, and SQL Server Agent by means of T-SQL query. This is made possible by the introduction of new DMV sys.dm_server_services.
This will return row for each of above services giving details on StartupType,Status,path where file exists etc.This comes handy when we have to report on the status of these services for monitoring purposes. 


The full documentation on this can be found in the below link
http://msdn.microsoft.com/en-us/library/hh204542(v=sql.105).aspx