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



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: 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 (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.



download





Recommended Reading

Finding a SQL Server process percentage complete with DMVs

How to Find Keywords in SQL Server Stored Procedures and Functions

Find SQL Server Missing Indexes with DMVs

Determine SQL Server memory use by database and object

Retrieve Actively Running T-SQL Statements from SQL Server














get free sql tips
agree to terms