THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search


A blog for members of the SQL Server SQLOS team to share information and get your feedback.

New DMVs in Denali

This article is about some of the new DMV’s that are newly added to next version of SQL Server. These are also available SQL Server 2008 R2 SP1. They are very helpful to get information about the host operating system that the SQL Server runs on, status of the server and even the memory dumps that are created by the server and registry keys that the server uses.

To read all mentioned DMV’s the user must have VIEW SERVER STATE permission.

sys.dm_os_windows_info:  This DMV returns the OS related information to the user. It is good to know the properties of hosting server.


  • windows_release:
  • windows_service_pack
  • windows_sku
  • os_language_version

sys.dm_server_registry:  This DMV returns some of the registry keys that the server uses.


  • registry key
  • value_name
  • value_data        

sys.dm_server_services: This DMV provides useful information about the SQL Server, SQL Agent and SQL FullText service related with the running server. This DMV is very helpful to track the servers status and startup configuration.


  • service_name
  • startup type
  • startup_type_desc
  • status
  • status_type_desc
  • process_id
  • last_startup_time
  • service_account
  • filename
  • is_clustered
  • cluster_nodename

 sys.dm_server_memory_dumps: This DMV returns the dumps that are generated by the server.


  • filename:
  • size_in_bytes
  • creation_time

 If you need more detailed information on any of those, please reply to this post or send email

Originally posted at
Published Thursday, May 05, 2011 12:41 PM by SQLOS Team



Ranga Narasimhan said:

Interesting...thanks for sharing..

I had put a connect item to expose Windows logs, SQL errorlogs thru DMVs, but it was closed.. won't fix.

May 6, 2011 4:30 PM

dugi said:

thnx for the info!

May 10, 2011 4:17 PM
New Comments to this post are disabled
Privacy Statement