SQL SERVER – Too Many SQLDump Files Consuming a Lot of Disk Space. What Should You Do?

It is not a normal behavior of SQL Server to generate minidumps. It is like a stress signal in SQL. When there is something “unexpected”, SQL Server kills the SPID and generates a dump. There are situations where SQL threads are not yielding (“Non-yielding Scheduler”, “Non-yielding IOCP Listener” or “Non-yielding Resource Monitor”) and there would be data needed to diagnose the problem later, so a dump is generated. These dumps are in the folder which has ERROLROG files. They can be triggered manually also using SQLDumper.exe for SQLDump.

Here is an image taken from one of my clients who contacted me for assistance.

What you should do?

First, you need to figure out from the ERRORLOG file the cause of the dump. Check SQL ERORRLOG to see an indication of something going wrong, with a timestamp in the error log around the same time as the creation time of the dump file. If you are not a SQL expert, then you can refer below blog to find more about ERRORLOG. SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location

You would notice that each SQL Dump file has three kinds of files.

1. SQLDump<nnnn>.txt
2. SQLDump<nnnn>.log
3. SQLDump<nnnn>.mdmp

Where nnnn is a number.

First two files can be opened using notepad (or any other text editor). Here is little information about them.

1. TXT file will have information like below.

This file is generated by Microsoft SQL Server
version 13.0.4206.0
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.

Computer type is Intel(R) Xeon(R) CPU E5-2660 0 @ 2.20GHz.
Bios Version is VRTUAL – 5001223
BIOS Date: 05/23/12 17:15:53 Ver: 09.00.06
4 X64 level 8664, 10 Mhz processor (s).
Windows NT 6.2 Build 9200 CSD .

It would also have details about what caused it.

2. File with “LOG” extension is a snippet of ERRORLOG file when the dump was generated. This would help someone to look at exactly Errorlog part when the dump was generated.
3. File with “MDMP” extension (called as minidump file) is a small memory footprint of the SQL process when the issue happened. There are blogs on the internet which can tell how to read them and I am not going to explain that in this blog. The Microsoft support team has ways to read that file and get more details using private symbols.

As an end user of SQL Server, you should look at LOG and TXT file to understand the cause of the error. You should look at the pattern of the dump by looking at the time and opening TXT and LOG files for each of them. If you see something like below, then its due to corruption in the database.

2017-09-28 17:41:27.85 spid20s ***Stack Dump being sent to F:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0002.txt
2017-09-28 17:41:27.85 spid20s * *******************************************************************************
2017-09-28 17:41:27.85 spid20s *
2017-09-28 17:41:27.85 spid20s * BEGIN STACK DUMP:
2017-09-28 17:41:27.85 spid20s * 09/28/16 16:40:26 spid 20
2017-09-28 17:41:27.85 spid20s *
2017-09-28 17:41:27.85 spid20s * ex_raise2: Exception raised, major=52, minor=42, state=9, severity=22

You should run DBCC CHECKDB. If the output from DBCC CHECKDB indicates corruption in your indexes, rebuild them. If it indicates that “repair_allow_data_loss” is the minimum necessary to repair the database, then restore from last known good backup, when checkdb was clean.

If it is anything like below, then make sure you have applied the latest service pack and a cumulative update of SQL Server.

1. Non-yielding Scheduler
2. Non-yielding IOCP Listener
3. Non-yielding Resource Monitor
4. Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
5. A system assertion check has failed. Check the SQL Server error log for details.

If you have taken care of patching and CHECKDB is clean but still get dump files, then my advice would be to contact Microsoft SQL Support team. There are high chances that it might be an issue with SQL Server product and Microsoft team might fix it.

If you are lazy and not interested to know the cause, go ahead and delete the files. They would be generated again, and you need to write a batch file to delete them on a regular basis. If your log folder has several dumps for a few years ago and then no dumps for several months, then a few recent dumps, you can safely delete the old dumps.

Have you found this useful? Please comment and share your experience about the dump.

Reference: Pinal Dave (https://blog.sqlauthority.com)