Search This Blog

Saturday, January 28, 2012

DBCC CHECKDB WITH WITH ESTIMATEONLY on MASTER Results 4 Rows

Hi All,

Today while working on something I figure out something. When we run DBCC CHECKDB (master) WITH ESTIMATEONLY the result set has 4 rows. Why?

Command:

DBCC CHECKDB (MASTER) WITH ESTIMATEONLY

Output:

Estimated TEMPDB space needed for CHECKALLOC (KB)

-------------------------------------------------
240

(1 row(s) affected)

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

(1 row(s) affected)

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

(1 row(s) affected)

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

(1 row(s) affected)

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

So in the above output we are seeing that there 2 entries for CHECKALLOC and CHECKTABLE.

WHY?

Anser is: The second entry is for RESOURCE DATABASE.

Try running DBCC CHECKDB on MASTER and in the output you will see that there will be 2 outputs (One for MASTER db and other for RESOURCE db). Even as per books online:

when DBCC CHECKDB is executed against the master database, a second CHECKDB is also run internally on the Resource database. This means that DBCC CHECKDB can return extra results. The command returns extra result sets when no options are set, or when either the PHYSICAL_ONLY or ESTIMATEONLY option is set.



 
Regards
Gurpreet Singh Sethi

No comments: