Few weeks ago, we had a big issue by a customer and a lot SQL Dumps was created in the SQL Server log folder.
After playing fireman at the customer’s premises, we decided to set up a monitoring system for the creation of SQL dumps.
The first step, is how to test it?
In the latest version of SQL Server the 2019 & the 2022, you can run the command:
DBCC STACKDUMP
This command will generate the 3 dump files (SQLDumpxxxx.log, SQLDumpxxxx.mdmp & SQLDumpxxxx.txt) with the full memory dump in the Default Log directory. I just do it on my test VM under SQL server 2022:
For the previous version of SQL Server (and also the last one) , you have the possibility to use Sqldumper.exe in the Shared folder:
Questions about your own setup? A dbi services consultant can look at your case.
Talk to an expert (opens in a new tab)
You can find more information here
Now, how to see with a T-SQL Command when a SQL Dump is generated?
The easy way is to use the dynamic management view (DMV): sys.dm_server_memory_dumps
This view will return one row for each memory dump (I run 3 times DBCC STACKDUMP to have more then 1 line):
My goal is to check one time in the day if some dumps are generated.
How can I check if some dumps are generated the last day?
From this DMV, I will pick the date of the last file generated and compare it to now:
DECLARE @Last_Dump_File DATETIME2;
SET @Last_Dump_File = NULL;
SELECT TOP 1 @Last_Dump_File=creation_time FROM sys.dm_server_memory_dumps WITH (NOLOCK) ORDER BY creation_time DESC OPTION (RECOMPILE);
SELECT @Last_Dump_File
IF (@Last_Dump_File > GETDATE()-1)
BEGIN
Select * from sys.dm_server_memory_dumps
ENDA little illustration:
How to monitor and have an alert?
To finish, I create a job with a step with this query and sending a email to our Service Desk in case of dumps coming by our customer the last day.
Example of email:
It is also the opportunity to tell you that this monitoring/alerting is included now in our DMK Maintenance, our tool to help the management of the SQL Server environment.
Don’t hesitate to come back to us for more information