How to be sure that tempdb size is good to run a DBCC CHECKDB?

In many blogs or forums, you can read that the answer is to use the option: WITH ESTIMATEONLY.
With this option, you can easily have the space estimation needed to check the database in tempdb. But be careful, only since SQL Server 2014, this estimation has been good!

See the PS from Paul Randal’s blog for this information, here

But between SQL server 2008, 2012 and 2014…and 2016, the result of this query changed! 😕

This article is to give you a generic script to estimate the size of the tempdb for a DBCC CHECKDB.

Option ESTIMATEONLY for DBCC CHECKDB

SQL Server 2008 & SQL Server 2008R2

In SQL Server 2008/R2, the result is 2 tables.

The first table is the estimated tempdb space needed for CHECKALLOC and the second for CHECKTABLES.

I don’t need to add both to have the space required. I need to have the greater value.

SQL Server 2012

In SQL Server 2012, the result is just a message (no tables).

SQL Server 2014

In SQL server 2014, the result is the same as SQL Server 2012

SQL Server 2016

In SQL server 2016, the result is the same as SQL Server 2014…No changes!

Option TABLERESULT for DBCC CHECKDB

For SQL Server 2008/R2, the result doesn’t change. I have the 2 tables without this option.

SQL Server 2008

But for SQL Server 2012, the table result is 2 rows with an error number.
The information that I need is in the row with the error Number 5281 in the column Message Text

SQL Server 2012

And for SQL Server 2014 and SQL Server 2016, the result is just one row with the error 5281. It’s good, it’s an useful row! 🙂

SQL Server 2014

SQL Server 2016

I checked the result for the latest versions of SQL Server, the difference is between 2008 and 2012/2014/2016. To create my script, I’m based to SQL Server 2012 and later.

Generic script for SQL server 2012, 2014 and 2016

I create a temporary table to store the result and I will just select all user databases (dbid>4).
I use the store procedure sp_MSforeachdb to run the DBCC CHECKDB through all user databases.

SQL Server 2012

SQL Server 2014 & SQL Server 2016

Then I select only the line with the error number 5281 to be compatible with SQL Server 2012, and I parse the message_text column to have the estimated tempdb space in an integer format.

CHARINDEX('=',message_texte)+1

is to have position before the number

CHARINDEX('.',message_texte)-CHARINDEX('=',message_texte)-1

is to have the length between the ‘=’ and the ‘.’

Then I have the substring and I cast the result in an integer to have the size value in KB.
I apply the T-SQL Command MAX() to have the value of the estimated tempdb space

I test with SQL Server 2014 and its running good!
Don’t forget that it has worked only fine since SQL Server 2014! 😉
Perhaps, with luck, in the next CU or SP, SQL Server 2012 will have a correction but I hear nothing for the moment…
Finally, I give you my generic script:

CREATE Table #checkdb_result
(
	error int,       
	level int,
	state int,
	message_texte varchar(7000), 
	repair_level int,
	status int,
	db_id int,
	db_frag_id int,
	object_id int,
	index_id int,
	partition_id int,
	alloc_unit_id int,
	rid_db_id int,
	rid_prud_id int,
	file_id int,
	page int,
	slot int,
	ref_db_id int,      
	ref_prud_id int,
	ref_file int,
	ref_page int,
	ref_slot int,
	allocation int 
)

EXEC sp_MSforeachdb
'IF(DB_ID(N''?'')>4) INSERT INTO #checkdb_result EXEC (''DBCC CHECKDB([?]) WITH ESTIMATEONLY, TABLERESULTS'')'

SELECT MAX (CAST(SUBSTRING(message_texte, CHARINDEX('=',message_texte)+1,CHARINDEX('.',message_texte)-CHARINDEX('=',message_texte)-1)AS INT)) AS max_estimated_tempdb_space_KB 
FROM #checkdb_result WHERE error=5281

DROP TABLE #checkdb_result