sys.dm_server_memory_dumps

Memory dumps are a copy of what lies in the RAM during a particular scenario such as a crash or even a manual Dump. Memory dumps are useful when working with any application because it helps identify exactly what was running on a system when the problem occurred. However with certain applications such as sql server the memory dump can be very huge. This is because there are mainly three types of dumps. These include the mini dump which is the default dump format in SQL server, the filtered dump which is the snapshot of what’s in the Ram with exception to the Buffer pool and then the Full Dump which is a complete Dump of everything that is in the RAM. It becomes clear that when taking FULL Dumps on system with say 64 GB RAM the single DUMP file can be about 60 GB which is a significant amount of space and can cause the system to run out of space. The above DMV helps capture the number of such dump files and the space occupied by them. This will help the BDA understand if there are any files that can be deleted or are obsolete or if he needs to change the way the Dumptrigger works so that it doesn’t generate many dumps files.

Most of the time the DBA won’t need to use this particular DMV unless he is already trouble shooting an issue.

 

The above screen shot shows the execution results for the DMV , for further details please visit the MSDN article here. For further details on how to create a dump file like here.