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.