THE SQL Server Blog Spot on the Web

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

SQLOS Team

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.

Columns:

  • 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.

Columns:

  • 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.

Columns:

  • 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.

Columns:

  • 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 http://blogs.msdn.com/b/sqlosteam/
Published Thursday, May 05, 2011 12:41 PM by SQLOS Team

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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.

https://connect.microsoft.com/SQLServer/feedback/details/543213/sql-dmvs-to-read-sql-errorlog-windows-nt-log-for-application-security-system-etc

May 6, 2011 4:30 PM
 

dugi said:

thnx for the info!

May 10, 2011 4:17 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement