SQL Server: how do monitor SQL Dumps?

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:

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
END

A 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