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.
Research
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.
So, 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.
Conclusion
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.
Resources:
The post Checking your dumps!!! appeared first on GarryBargsley.com.