Memory Dumps

What this check looks for

The ten most recent rows in sys.dm_server_memory_dumps with a creation_time in the last 5 days. Each finding gives the time and the dump file name.

Why it matters

SQL Server does not write a memory dump as a routine event. It writes one when it hits a condition it was not designed to survive, and it dumps its own state so the problem can be diagnosed afterwards. The check’s own description puts it plainly: this is usually a sign that SQL Server has crashed lately, and that is a very bad sign.

What produces one:

  • An access violation. A genuine engine bug. The connection is killed and often the whole instance restarts.
  • A non-yielding scheduler. A worker held a scheduler without yielding for long enough that SQL Server concluded something was stuck. Often a driver, filter driver or antivirus product in the I/O path rather than SQL Server itself.
  • A latch or lock timeout deemed fatal, or an assertion failure, where the engine has detected its own internal state is inconsistent.
  • Stack overflows from deeply nested code, and out of memory conditions inside internal structures.

Two things follow that make this Critical rather than merely interesting:

It probably happened while people were using the system. An access violation kills the session. A non-yielding scheduler stalls everything on that scheduler. A dump usually means somebody experienced an outage and may have reported it as “the application was slow”.

An assertion failure means the engine found its own state inconsistent, which is adjacent to corruption. That is why this check is filed under Corruption rather than under Performance, and it is why the first response includes a DBCC CHECKDB.

The dumps also accumulate. Each is tens or hundreds of megabytes, and on an instance dumping repeatedly they quietly fill the log directory, which is often the system drive.

How to confirm it yourself

SELECT [creation_time],
       [filename],
       [size_in_bytes] / 1024 / 1024 AS [size_mb]
  FROM sys.dm_server_memory_dumps WITH (NOLOCK)
 ORDER BY [creation_time] DESC;

The error log is where the reason is. Every dump is preceded by an explanation:

EXEC sp_readerrorlog 0, 1, N'dump';
EXEC sp_readerrorlog 0, 1, N'Non-yielding';
EXEC sp_readerrorlog 0, 1, N'Access Violation';
EXEC sp_readerrorlog 0, 1, N'stack dump';

Read the older logs too, because a dump that ended in a restart moved the current log along:

EXEC sp_enumerrorlogs;
EXEC sp_readerrorlog 1, 1, N'dump';

And check whether the instance restarted, which tells you whether it was fatal:

SELECT [sqlserver_start_time] FROM sys.dm_os_sys_info WITH (NOLOCK);

How to fix it

A dump is a symptom with a cause recorded next to it. Read the cause first.

  1. Find the matching error log entry. It names the type. That single word decides everything that follows, because a non-yielding scheduler and an access violation lead in opposite directions.
  2. Run DBCC CHECKDB on every database. Not optional. An assertion failure in particular can accompany corruption, and you want to know now rather than during a restore.
  3. For a non-yielding scheduler, look outside SQL Server. The usual suspects are antivirus scanning data or backup files, a filter driver, a storage path problem, or a failing HBA. Excluding .mdf, .ndf, .ldf and .bak from real time scanning is the standard first move and it is free.
  4. For an access violation or assertion, get current on patches. These are engine bugs and most are fixed; being several cumulative updates behind makes it likely yours already has a fix. If you are current, this is a support case, and the dump file is the evidence Microsoft will want.
  5. Tidy up afterwards, once the cause is understood. The dump files can be deleted, and on an instance that dumped repeatedly they are worth checking for as a disk space problem in their own right. Keep at least one if a support case is open.

If this instance is on one of the builds with a known corruption bug, treat the two findings together: see the dangerous version check.

How long it takes

About four hours to read the logs, run CHECKDB and establish the category. Patching or a support case is a separate piece of work with its own window.

Report Why you would go there
Error Log The entry that explains the dump, which is the whole diagnosis.
Suspect Pages Whether corruption accompanied it.
Last DBCC CheckDB Known Good by Database Whether anything has verified the data since.
Server Overview The build, and how long the instance has been up.
Performance History What the instance was doing at the time.
Disk Space Whether the dumps are filling the log directory.
Check
SQL Server recently restarted Often the same event, seen from the other side.
Dangerous SQL Server Version Builds where the engine can corrupt data on its own.
DBCC CHECKDB Corruption Errors Found The check that would catch the consequence.
Serious errors in log The severity 19 to 25 entries that usually accompany a dump.
Version updates available Being behind on updates, which is the common fix.

Frequently asked questions

The instance is up and everything works. Is this still urgent? Yes. The dump records something that already went wrong, and the conditions that produced it have not changed by themselves.

Can I just delete the dump files? Once you know what caused them, yes, and reclaiming the space is worth doing. Deleting them before reading the error log throws away the only record of a crash.

We get one every few weeks. That is a pattern rather than an incident, and patterns get diagnosed. Compare the error log entries: the same stack every time points at one repeatable trigger.

Why five days? So the check reports a recent crash rather than one from last year. The query above shows every dump the instance still holds.