SQL SERVER – Drive Space Full Due to MDMP Files

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?

SQL SERVER - Drive Space Full Due to MDMP Files Dumps

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://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – InMemory OLTP Migration Assistant Powershell Script
Next Post
SQL SERVER 2016 – Enhancements with AlwaysOn Availability Groups – Notes from the Field #121

Related Posts

4 Comments. Leave new

  • Hi Pinal,

    Thank you for being so consistent in sharing your knowledge and helping all of us to learn and grow !!

    As the database corruption seems not to be so small, did you get chance to figure out what caused corruption ? I know taking tail log back is recommended in such cases, I am not sure how it can be of help ?

    I mean some chuck of data got corrupted and column value for a row got changed from 10 to 20 or some garbage value – SQL Server would record old and last value in the log file and my guess is it won’t do any checks. In that case, restoring tail log back would worsen the situation.

    I have misunderstood some thing – Appreciate if you could you please me in getting clarity on tail log back up.

    Thank you.

    Br,
    Anil Kumar
    SQL DBA

    Reply
  • Hi,
    We’ve got the same problem with a client with SQL Server 2008 R2: upgraded at SP4 the problem mysteriously disappear…

    Reply
  • Vicky Sukhwani
    June 27, 2016 5:15 pm

    Recently we had Prod Db failure on our environment i can see *.mdmp files were created in the Error Log directory when i was running CheckDB and finding corruption. I want to set up a SQL Alert which will notify me whenever a new mdmp file or SQLDUMP*.txt is created, can you please help me that how should i set it up. I looked everywhere but couldnt find anything.

    While setting up SQL Alert i need to give combination of Error Number/Severity or Message text if i now but looking SQL Server Error logs i dont see any Error Number or Severity which i can use so please suggest how can i setup automated Alerts to check if new Dump file was created on my environment

    Thanks in Advance

    Reply
  • joydeeptapadarJoydeep
    June 25, 2017 9:17 pm

    Thanks Pinal for posting your experience here and we got a good knowledge to read the thing.

    Reply

Leave a Reply

Menu