DBCC CHECKDB Error | MSSQL_DBCC: Operating system error 112(There is not enough space on the disk.) encountered.

Today at SQL Server Central, I responded to one of the questions which was related to space issue. The error message read

E: ******* ProductionData.mdf:MSSQL_DBCC10: Operating system error 112
(There is not enough space on the disk.) encountered.

The error message looks like there is not enough for the data file to grow.  But the real clue lies at the end of the path. It reads “:MSSQL_DBCC10”. This type of error message is logged in the SQL Server Error Log only on SQL 2005 and above. So what has changed?

From SQL Server 2005 onwards, when DBCC CHECKDB is run against a database, an internal (hidden) read-only database snapshot is created. This database snapshot is created on the same location where the data file in question is located.

The snapshot will grow in proportion to the data change that occured after the DBCC CHECKDB was initiated against the database. When there is no more space available for the snapshot to grow, the above error message will be written in the error log.

The best option would be to move the data file to a drive which has sufficient space to grow. If this is not feasible,  the TABLOCK hint can be used in the DBCC CHECKDB command.

DBCC CHECKDB [databasename] WITH TABLOCK

The TABLOCK hint will not create Database Snapshot instead it will hold a short-term exclusive lock on the database. The downside to this approach is that the concurrent users will face intermittent performance issues and the checks that are performed by CHECKDB will be limited. Like the DBCC CHECKCATALOG will not be run when TABLOCK hint is used.

However, this is a good workaround till more space is added on the instance.

4 thoughts on “DBCC CHECKDB Error | MSSQL_DBCC: Operating system error 112(There is not enough space on the disk.) encountered.

  1. Paul S. Randal

    Neither of your suggested workarounds are good. The WITH TABLOCK will most likely fail as it needs an exclusive database lock to be able to perform the allocation checks – which is usually not possible on in-use user databases. Moving the data file to another location is also not feasible as it requires downtime.

    One alternative if there is no space for the hidden database snapshot I create is to create your own database snapshot and run DBCC CHECKDB on that snapshot – it’s semantically the same. The best alternative is to restore a full backup of the database to another server and run DBCC CHECKDB in it there – offloading all the workload from the production system.

    Thanks

    1. PradeepAdiga Post author

      Thanks Paul for your suggestions. You are right that WITH TABLOCK will “most likely” fail. In fact I ran CHECKTABLE on each table (not CHECKDB) on a 1TB database and did not face any issues.
      Creating a snapshot and running CHECKDB on it is also a better option. But in my case, the client would not give me 1 TB of space just to run CHECKDB.
      However, both the workarounds suggested by me have pros & cons. I had suggested the TABLOCK option assuming that the CHECKDB will be done during the scheduled down time.

  2. Pingback: Does tempdb runs out of space during DBCC CHECKDB? | SQL Server DBA Diaries of Pradeep Adiga

  3. Pingback: FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) | SQL Server DBA Diaries of Pradeep Adiga

Comments are closed.