Checking for SQL Server Dump Files After a Crash

SQL Server dump files are the first thing to check after a crash, and the last thing to delete. A short read-only query tells you whether any exist, when they were written and how big they are.

The Monday morning question

Picture a Monday morning. The service restarted sometime over the weekend, and your manager asks one thing: “Did SQL Server crash?” Restarts have many causes. A patch, a reboot and a real crash all look the same from the outside.

A real crash usually leaves evidence behind. When the engine hits a serious problem, it writes a memory dump file. Think of it as a snapshot of the process at the worst moment. Before you do anything else, find out whether that snapshot exists.

List the dumps and the last start time

The view sys.dm_server_memory_dumps lists every dump file the instance knows about, with its name, creation time and size. You need the right server-level permission to read it. The second query returns the time the instance last started. Put the two side by side. A dump written a few seconds before the start time tells a very different story from one written a month earlier.

SELECT filename, creation_time, size_in_bytes
FROM sys.dm_server_memory_dumps
ORDER BY creation_time DESC, filename;

SELECT sqlserver_start_time
FROM sys.dm_os_sys_info;

On my test server the first query returned no rows. That is a perfectly good answer: this instance had no dump files at that moment. Do not read it as proof that nothing ever went wrong. Files get moved, and logs get recycled. Your server will show its own values for the start time, so I will not quote mine.

Look for the story in the error log

Dump files rarely appear alone. The error log usually has a message around the same time. The procedure xp_readerrorlog reads it. The first number, 0, means the current log. The second, 1, means the SQL Server log, not the Agent log. The last parameter is the text to search for.

EXEC master.dbo.xp_readerrorlog 0, 1, N'dump';

Here is a lesson from my own run. Searching for the plain word “dump” matched ordinary backup and restore messages, because they say “pages dumped” and “dump devices”. So read what comes back before you panic, and tighten the search text if the output is noisy.

If the current log has rolled over, change the first number to 1, 2 and so on to read the older logs. A crash from last week may live in log number 3.

Check where the files would go

Dump files normally land in the same folder as the error log, so ask SQL Server where that is. The second query shows free space on the volumes that hold your database files. That matters because a dump of a big instance can be large. A full drive at the wrong moment turns a bad day into a worse one.

SELECT SERVERPROPERTY('ErrorLogFileName') AS ErrorLogFile;

SELECT v.volume_mount_point,
       MAX(v.total_bytes) AS TotalBytes,
       MIN(v.available_bytes) AS AvailableBytes
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS v
GROUP BY v.volume_mount_point
ORDER BY v.volume_mount_point;

You get one row per volume with its total and available bytes. Remember that the dump folder can be on a different drive than your data files. Match the actual folder before you trust the numbers.

What to do next

First, preserve. Copy the files somewhere safe and note their creation times, sizes and the build number. Do not delete or move a dump while the engine could still be writing to it. Second, protect them. A dump can contain memory from the process, including data, so keep the files private and never attach them to a public thread.

Third, do not guess the cause from a file name. A dump needs specialist analysis, and your support contact will ask for the file and the matching error log lines. Keep both together.

Next time somebody asks whether the server crashed, you will have an answer by lunch.

A dump listing is not a diagnosis, it is an inventory of evidence.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.