Discrepancy by estimated TEMPDB space for CHECKDB and CHECKALLOC - WITH ESTIMATEONlY
I came around this problem (depends on SQL Server 2008 R2 jutst to SQL Server 2012) by reading Paul S. Randal's and Ana's blog posts:
- How does DBCC CHECKDB WITH ESTIMATEONLY work (Paul)
- DBCC CHECKDB WITH ESTIMATEONLY: Do you trust it? (Ana)
DBCC CHECKDB runs CHECKALLOC, CHECKTABLE, CHECKCATALOG etc. against database, tables, views and so on. And I have learnded, however CHECKDB is executed against master database, a second CHECKDB is also running internally on the mssqlsystemressource.
So in my way, I want only make consistency check of disk space allocation by DBCC CHECKALLOC WITH ESTIMATEONLY against a ~ 200GB database, to know the estimated amount of tempdb space.
I run following DBCC command on SQL Server 2012 Enterprise Edition:
DBCC CHECKALLOC () WITH ESTIMATEONLY, TABLERESULTS;
GO
And I get this result
Estimated TEMPDB space (in KB) needed for CHECKALLOC on database XXX = 35383596.
OK, nothing special - but I thought about the posts of Ana and Paul, and a blog entry of Running SQP on SQL Server Blog - Faster DBCC CHECKDB Released in SQL 2008 R2 SP1 CU4 : Traceflag 2562 & 2549
So next shot comparing CHECKDB and CHECKALLOC:
DBCC CHECKDB WITH ESTIMATEONLY, TABLERESULTS;
GO
DBCC CHECKALLOC WITH ESTIMATEONLY, TABLERESULTS;
GO
And the results are diffrent!
For CHECKDB:
Estimated TEMPDB space (in KB) needed for CHECKDB on database XXX = 27165.
And for CHECKALLOC:
Estimated TEMPDB space (in KB) needed for CHECKALLOC on database XXX = 35383596.
This isn't an expected result - what a discrepancy!
By the way I have made a Microsoft Connect entry: http://connect.microsoft.com/SQLServer/feedback/details/787049/discrepancy-by-estimated-tempdb-space-for-checkdb-and-checkalloc-with-estimateonly
So feel free to vote.
I wish you a nice day,
tosc
Print article | This entry was posted by tosc on 2013-05-17 at 11:39:00 . Follow any responses to this post through RSS 2.0. |
Tag cloud
administration backup «best practices» books bug ctp «cumulative update» datetime demo dmv ebook humor index indexoptimize integrity kbfix links maintenance «ms sql server 2008» pass performance php reviews rtm serverproperty «service pack» «service pack 2» «service pack 3» «service packs» sharepoint sp_msforeachdb «sql server» «sql server 2005» «sql server 2008 r2» «sql server 2012» «sql server 2014» «sql server builds» sqlcat sqlpass «sqlpass franken» ssms ssmstoolspack «stacia misner» t-sql technet «technical note» tempdb tools version whitepapers