SQL Server Service Information

DMV: sys.dm_server_services

Microsoft SQL Server 2008 R2 SP1 and SQL Server 2012 has a new set of DMVs. One of them is “sys.dm_server_services” which  return a whole host of important information relating to the SQL Server services that includs;

  • Service Name
  • Startup Type
  • Startup_Type_Desc
  • Status
  • Status_Desc
  • Process_id
  • Last Startup Time
  • Service Account,
  • File Name
  • Is_Clustered
  • Cluster NodeName

Open up a new query window, and run the following select statement.

SELECT 
	process_id, servicename, status_desc, startup_type_desc,
	last_startup_time, service_account, is_clustered, 
	cluster_nodename, [filename]
FROM sys.dm_server_services WITH (NOLOCK);

When you run the above query, it is going to show below information.

I hope you enjoyed the DMV. It is a very useful DMV to have a quick view of the SQL Server services.

Reference: http://msdn.microsoft.com/en-us/library/hh204542.aspx

Leave a comment

Your email address will not be published. Required fields are marked *