CheckDB and TempDB
Its a reminder today, about sizing TempDB to fit the instances largest database for CHECKDB. Its also a good point to remind people that WITH ESTIMATEONLY doesn’t currently work properly on SQL Server 2012 and some versions of 2008 r2.
On a 60GB database:
DBCC CHECKDB (myDB) WITH estimateonly
DBCC results for 'myDB'. Estimated TEMPDB space (in KB) needed for CHECKDB on database myDB = 1. CHECKDB found 0 allocation errors and 0 consistency errors in database 'myDB'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Where as when we run CHECKDB against the database with TempDB set up with 4 files all 1 GB each the “Disk Usage” report from SSMS on TempDB is:
An impressive growth from 4gb to over 25gb and extending the CHECKDB run time substantially.
If you wondered what you’ll see in sys.dm_db_file_space_usage when CHECKDB runs, its a spike in internal_object_reserved_page_count. Below is a snip of a custom metric from SQL Monitor which is tracking Internal_objects (Note: Thanks to Paul Randal for pointing out that the DMV works on all databases in SQL server 2012! after my puzzled email looking when i was originally querying against Master!)