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?