Can we check the amount of tempdb space dbcc checkdb will require to execute?

Often database analyst get worried on running checkdb for it’s demand of excessive resource. Below query will give you a clear estimate of how much space it will require to execute checkdb on a specific database.

dbcc checkdb ([database name]) with ESTIMATEONLY

Example:

Below example will determine how much tempdb space will be needed to run checkdb on a database named ‘testdatabase’

dbcc checkdb (testdatabase) with ESTIMATEONLY

Example Output:

Estimated TEMPDB space needed for CHECKALLOC (KB)
-------------------------------------------------
6503

(1 row(s) affected)

Estimated TEMPDB space needed for CHECKTABLES (KB)
--------------------------------------------------
3187

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Note: You can run this command with out much worry on production as it does not really execute checkdb. Also you may be amused to find that checkdb does not really take huge amount of tempdb space on 2005/2008 onwards. Actually 2005 onwards checkdb uses snapshot of the database which reduces the overhead of running checkdb on a database to a great extent.

Comments

Popular Posts