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

By:   |   Comments (3)   |   Related: > Dynamic Management Views and Functions


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.


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

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 

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 

sys.dm_server_registry Dynamic Management View
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

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

All of these DMVs appear to already be available in SQL Server 2008 R2 after you apply SP1.  Source:

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

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 (15233)

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

get free sql tips
agree to terms