New SQL Server 2012 DMVs to get configuration, installation and memory dump information


By:   |   Updated: 2011-11-29   |   Comments (3)   |   Related: More > Dynamic Management Views and Functions

Problem

SQL Server 2012 has a lot of new features to offer.  A new set of features include new DMVs that can return SQL Server configuration, installation and memory dump related information.

Solution

In this tip we will discuss how to utilize the below dynamic management views which are new in SQL Server 2012.  It is important to be aware that on every SQL Server service restart the data in these Dynamic Management Views is reset.

  1. sys.dm_server_memory_dumps
  2. sys.dm_server_services
  3. sys.dm_server_registry

Using sys.dm_server_memory_dumps Dynamic Management View

The sys.dm_server_memory_dumps dynamic management view returns one row for each memory dump file that is generated by the SQL Server Database Engine. If you notice entries in this view then you should alert a Senior DBA so they can check what caused the memory dumps which could lead to data corruption.  I also recommend you read the following article Finding which queries were executing from a SQL Memory Dump which explains how to find the queries that were executing when the memory dump were created.

SELECT * FROM sys.dm_server_memory_dumps
GO

sys.dm_server_memory_dumps Dynamic Management View

Using sys.dm_server_services Dynamic Management View

The sys.dm_server_services dynamic management view returns information with respect to startup type, running status, when service was last started, etc... for SQL Server, Full-Text, and SQL Server Agent services for the current SQL Server instance.

SELECT * FROM sys.dm_server_services 
GO

sys.dm_server_services Dynamic Management View

Using sys.dm_server_registry Dynamic Management View

The sys.dm_server_registry dynamic management view returns SQL Server Configuration and Installation information which is stored in the Windows Registry for the current instance of SQL Server. This is a very useful DMV which can quickly give you relevant information with respect to the Current Version, Path of Master Database, Error Log files, etc... as shown below.

SELECT * FROM sys.dm_server_registry 
GO

sys.dm_server_registry Dynamic Management View
Next Steps


Last Updated: 2011-11-29


get scripts

next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, November 29, 2011 - 9:20:36 AM - Aaron Back To Top

All of these DMVs appear to already be available in SQL Server 2008 R2 after you apply SP1.  Source: http://blogs.msdn.com/b/sqlosteam/archive/2011/05/11/more-on-dmv-changes-in-sql-2008-r2-sp1.aspx


Tuesday, November 29, 2011 - 8:37:57 AM - Luke Campbell Back To Top

Nice article and thanks for sharing!  These views are available in SQL Server 2008 R2 SP1 as well.


Tuesday, November 29, 2011 - 5:10:38 AM - John Back To Top

Thanks for sharing this tip. It will be very usefull.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools