New DMV in SQL Server 2012: sys.dm_server_services

In this blog we shall learn a new Dynamic Management View introduced in SQL Server 2012 called sys.dm_server_services. This dmv can be used to query the startup type, current status, last_startup_time, service accounts under which the service starts, is the service clustered and other related information.

SELECT * FROM sys.dm_server_services

The dmv returns output with 11 columns, for better readability, the output is broken down. The data displayed is for the 3 services, SQL Server, SQL Server Agent service and Full-text. The below screenshot displays only 2 rows (for SQL Server and SQL Server Agent) as Full-text is not installed.  The following are some of the important startup_type codes: 2 – Automatic, 3 – Manual, 4 – Disabled. Some of the status codes are 1 – Stopped, 4 – Running, 7 – Paused etc.. 

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

  • Sejal Gudhka

    Now automation of tracking downtime will become very easy with the help of new dmv…..sys.dm_server_services.

  • http://www.LearnSQLWithBru.com Bru Medishetty

    Yes..

  • Hemanth Kumar reddy

    good post. We have one more option to check the server start time