If you are a DBA and have encountered drive space full issues on your SQL Server machine, you may have initially assumed that the problem was caused by a large transaction log file. However, there can be other factors contributing to this issue. In this article, we will explore one such factor – SQL Server dump files.

Recently, one of my clients contacted me about running out of drive space on their C Drive. Upon investigation, I discovered that the transaction log files were located on a different drive and were not excessively large. To identify the cause of the space consumption, I downloaded a tool called Windirstat, which helped me analyze the disk usage. Surprisingly, I found that most of the space on the C drive was occupied by SQLDUMP files.

So, what exactly are SQL dump files? In general, stack dumps are a serious problem. They indicate that something is not functioning as expected in SQL Server, leading to either the termination of a process or the termination of SQL Server itself. Each dump file consists of three parts: a text file, a log file, and an mdmp file. While the text and log files are readable, the mdmp file requires a special tool called Windows Debugger to be read.

Let’s take a look at the content of a readable dump file:

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 corresponds to error number 5242. To understand the meaning of this error, we can use the following query:

SELECT *
FROM sys.messages
WHERE message_id = 5242
AND language_id = 1033

The result of this query indicates that an inconsistency or corruption was detected during an internal operation in the database 'SAPDB' (ID: %d) on page %S_PGID. This means that there is an issue with the database itself.

Now, the question arises – which database is affected? To find the answer, I captured a Profiler trace and identified the message containing the database name. Upon running DBCC CHECKDB on the database, it reported corruption. The CHECKDB command revealed 0 allocation errors and 420 consistency errors in the ‘SAPDB’ database. The minimum repair level for these errors, as suggested by DBCC CHECKDB, was ‘repair_allow_data_loss’.

Since my client had a recent good backup, I advised them to restore the database instead of opting for the ‘repair_allow_data_loss’ option. This ensured that the database was restored to a consistent state without any data loss.

Have you ever encountered a situation where the log directory had too many dump files, resulting in a full drive space? If so, it is essential to identify the cause and take appropriate action to resolve the issue. In this case, the SQL dump files were generated due to database corruption, which required a database restore.

As a DBA, it is crucial to regularly monitor disk space usage and investigate any unexpected consumption. By understanding the various factors that can contribute to drive space issues, you can effectively manage and maintain your SQL Server environment.