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)
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