DBCC CHECKDB grows tempdb to a huge size - Legacy MS SQL Server 2008 R2 Instance

Question

I have an older Microsoft SQL Server Instance (2008 R2 SU3 GDR - Build: 10.50.6220). When I run DBCC CHECKDB via a Maintenance Plan, the TempDB grows very large.

The largest DB on the instance is 95GB, and while running a data integrity check tempdb grew to 40GB before filling up the filesystem.

Here's the error message:

Executing the query "DBCC CHECKDB(N'[dbname]') WITH NO_INFOMSGS " failed with the following error: "Could not allocate space for object 'dbo.SORT temporary run storage: 140907993825280' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

My first action step is to move tempdb to its own drive. However, I'd like to find a way to run a full CHECKDB (not PHYSICAL_ONLY) that uses less tempdb space. I can't upgrade to a newer version of SQL Server at this time.

Any helpful tips?

Answer 1

Actual serious although unusual suggestion: drop all the indexes in the database - or some of them - then "DBCC CHECKDB" has fewer things to check. Of course you can put them back after...

Alternatively, rebuilding indexes may produce a simpler arrangement of data for CHECKDB to analyse... this is actually a guess, but you may use fewer pages to store a table, at least.

https://technet.microsoft.com/en-us/library/ms176064(v=sql.105).aspx describes several WITH options which increase or decrease the workload of CHECKDB, and another option ESTIMATEONLY which causes it do skip any checking but tell you how much space in tempdb is required for checks. It's implied that this varies when you set other parameters with ESTIMATEONLY i.e. TABLOCK, PHYSICAL_ONLY. These do skip some checks.

Most but apparently not all of the checks in CHECKDB can be run as different commands, as described.

https://www.mssqltips.com/sqlservertip/2399/minimize-performance-impact-of-sql-server-dbcc-checkdb/ has performance-related suggestions including restoring the database backup to a different server to be checked. This too might not be a perfect check if the restore server is running a later SQL Server version and quietly "fixes" flaws of the original database during restore, so that you don't see them in the copy.