Does tempdb runs out of space during DBCC CHECKDB?

Recently I read one of the forum posts where the user was running DBCC CHECKDB against one of the biggest databases on an instance. But CHECKDB will terminate abruptly reporting that the tempdb had run out of space. Starting SQL Server 2005, SQL Server makes use of Database Snapshots while running CHECKDB. The snapshot is created on the same drive where the data file is located. If there is no enough space for the snapshot to be created, CHECKDB will fail reporting the error. I had come across this issue and blogged about it here.

But in this case, CHECKDB was failing because of space constraints on tempdb. Why does tempdb come into picture while running CHECKDB? While running CHECKDB, SQL Server stores the information related to the Database in memory. If the database is huge or if there is not enough memory, SQL Server is forced to store this information in tempdb. Hence the tempdb grows and if there is not enough space for the tempdb to grow, CHECKDB will fail. This article on MSDN explains how to Optimize DBCC CHECKDB Performance.

DBCC CHECKDB has an option to check how much space is required by tempdb. This estimation can be obtained before running DBCC CHECKDB against a given database.

DBCC CHECKDB ('databasename') WITH ESTIMATEONLY

Here is the estimated space needed for tempdb to run CHECKDB against a 25 GB database.

This option helps to size the tempdb properly, before running DBCC CHECKDB on a big database.

Whenever I hear DBCC CHECKDB, the first name that comes to my mind is Paul Randal (blog | twitter). His posts in the CHECKDB From Every Angle series are the best source of information available on the internet regarding DBCC CHECKDB.

Leave a Reply