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.
Where nnnn is a number.
First two files can be opened using notepad (or any other text editor). Here is little information about them.
- TXT file will have information like below.
This file is generated by Microsoft SQL Server
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.
- 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.
- 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.
- Non-yielding Scheduler
- Non-yielding IOCP Listener
- Non-yielding Resource Monitor
- Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
- 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)
thank you for this handy tips. Time to save my database space!!!
“If you see something like below, then its due to corruption in the database.”
What part of the dump indicates corruption?
That is a great question. I will have to write a follow up blog post on it.
Thank you so much!
Hello. How can I understand in which database the problem occurred?
It is Microsoft SQL Error. Just turning on the computer, no processing made, SQL dumps error file is automatically created.
There is same kind of issue for SQL Server 2019 Polybase.
Too Many Dump Files Consuming a Lot of Disk Space. How to fix this issue?
Had to kill MS-SQL 2019 processes, uninstall it, then manually delete over 250 Gb of dump files!
The part that matters to me is the “delete the files” part. I am a developer and the dumps are the result of mistakes I have made. I do not need the dumps yet there are many of them in the MSSQLLog directory. Can I delete everything there or should I be selective about what files to delete? Perhaps I should delete all txt, log and mdmp files. Some dumps also have cab, ddf, inf and rpt files. So perhaps the strategy should be to delete all SQLDump files older than a specified length of time. There are other files there but nothing large enough to cause concern about space used.
simple solution is to install space sniffer tool, view how your disk space is utilized, the delete the SQL dump mini-folder from space-sniffer. Space-sniffer is free and works well. Hope this help.