If you are a DBA and if you ever hear about drive space full issues on the SQL Server machine, the first thought to come to your mind would be “huge transaction log file”. Recently I have also fallen into the same trap. One of my clients contacted me and informed that they are running out of drive space on C Drive. I looked around and found that the transaction log files were located on a different drive and the size of them was not huge.
I downloaded http://windirstat.info/ and found that most of the space C drive was taken by SQLDUMP files. I was wondering what is the SQL Dump file for?
As you can see in the animation, the dumps were getting generated at a rapid rate. In general, stack dumps are a serious problem. Dumps indicate that something is not working as expected in SQL Server and forcing SQL to either terminate the process or terminate itself.
Each dump had three files generated. Out of them text and log were readable, but mdmp needs a special tool (windows debugger) to read it. Here is what I saw in the files which were readable.
SQLDump9999.txt
********************************************************************************
* BEGIN STACK DUMP:
* 10/16/15 19:28:15 spid 22
* ex_raise2: Exception raised, major=52, minor=42, state=9, severity=22
* ******************************************************************************
Above exception major=52, minor=42 is equal to error number 5242. I have used below query to find meaning.
SELECT * FROM sys.messages WHERE message_id = 5242 AND language_id = 1033
An inconsistency was detected during an internal operation in database ‘%.*ls'(ID:%d) on page %S_PGID. Please contact technical support.
This means that there is inconsistency or corruption in the database. Now the question was which database? So, I captured Profiler and found the message having database name. When we ran DBCC CHECKDB on the database, it reported corruption.
CHECKDB found 0 allocation errors and 420 consistency errors in database ‘SAPDB’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SAPDB).
Since, they had a last known very recent good backup, I asked them to restore rather than running repair_allow_data_loss.
Have you ever had such situation where log directory has too many dump file and got your drive space full? What was the cause and what you did to fix it?
Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
