June 11, 2015 at 8:42 pm
I have 3.5TB database( no partition, has multiple data files), estimates on this databases shows that DBCC Checkdb with all options should take about 452 MB of tempdb space ( 452 MB for CheckAlloc, 1 MB for Checktable) , after letting it run for 9 hours, i see that it has used more than 75GB space on tempdb data file and more than 200GB on log file for tempdb, is there a better way to get estimates on tempdb usage?
June 12, 2015 at 2:50 am
Known bug
http://www.sqlskills.com/blogs/paul/how-does-dbcc-checkdb-with-estimateonly-work/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2015 at 11:03 am
Makes sense. I did enabled traceflag 2562, now the estimated for CheckAlloc is 450MB and for CheckTable is 750GB !!!. Looks like enabling trace flag 2562 and getting estimates is the way to go. Does the estimate including data and log file usage for tempdb?
June 12, 2015 at 1:54 pm
Not sure, but I suspect it'd be data file usage.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply