No, No, not those dumps: SQL Server Memory dumps!!
Recently, I was on Slack and wondered into the #firstresponderkit channel. I am a big fan of all the First Responder Kit utilities by Brent Ozar ( b | t ). I don’t usually poke my head in this channel. This particular day I was browsing around and saw a question from DK.
Luckily, I have a little experience with the SQL Server Memory Dumps and this DMV. I wrote the Get-DbaDump command for dbatools back in the day. At the time, I had done research about the process and the DMV. There is not much to the function, but it finds what you are looking for.
Just out of curiosity, I ran the command Get-DbaDump against my local test server where I knew there had been a few SQL Server Memory Dumps. It returned that there were four Memory Dumps. Then I ran sp_Blitz to see what DK was referring to. My run of sp_Blitz did not return a reference to any Memory Dumps. This was strange to me since I know there are four on my test server. In this day of Open Source Projects, I proceeded to open the sp_Blitz stored procedure and look around at the code. What I found was the procedure had logic to only report if 5 or more Memory Dumps were returned from the sys.dm_server_memory_dumps DMV. In my opinion, this was high for this type of check.
Into GitHub, I go to open an Issue. Another awesome benefit of an Open Source Project that is actively monitored and developed. I opened an issue and created a pull request with a code change to trigger results if at least one Memory Dump is detected.
Luckily, the project maintainer Brent Ozar agreed with my suggestion. This change was accepted from the pull request. Now when you run sp_Blitz any Memory Dump found will report results. (Version 7.99 or greater)
Now back to the original topic of the question. The sp_Blitz check uses the sys.dm_server_memory_dumps DMV as mentioned to check if any Memory Dumps have occurred. This DMV tracks the *.mdmp files located in your default LOG directory for SQL Server. As long as you leave your Memory Dump files in the LOGS directory they will continue to persist in the results of the DMV. Once you remove the Memory Dump files from the disk or move them to another location the results from running the DMV will be reflected.
For my environment, I have a daily job that executes to check if any Memory Dumps have occurred on any servers. If that report hits my inbox, I know I have some investigating that needs to be done.
If you found this blog handy, please use the Subscribe section at the top of the screen to join my newly created mailing list. I hope to start delivering content via the mailing list soon. 👆