Jan 2016 Tip of the Month: sys.dm_server_memory_dumps

January 2016 Tip of the Month:  Using sys.dm_server_memory_dumps to find SQL memory dumps.

When SQL experiences a serious issue it will often dump the contents of its memory to disk. This is known as a memory dump and it does generate files that are usually located in the Logs folder. I often find that many SQL DBA’s are unaware that SQL may be experiencing issues until they start seeing some scary looking messages in their error logs. It is possible for SQL to have a serious issue but not bad enough to cause it to shut down. I have also found that the memory dump files can be quite large and may need to be cleaned up periodically. If you query the dynamic management view sys.dm_server_memory_dumps, you will get a list of memory dumps along with the location of the files.

SELECT [filename], creation_time, size_in_bytes

FROM sys.dm_server_memory_dumps

If you find that SQL is producing memory dumps, you should be alarmed and may need to send the files off to Microsoft for evaluation. You can find more on the sys.dm_server_memory_dumps on MSDN at: https://msdn.microsoft.com/en-us/library/hh204543.aspx