2

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?

  • 1
    You may need to segment out the checkdb to it's constiuent components. Look to do indivudal checktable executions and manually create snapshots to run them. A free product such as minionware.net/checkdb many help you with this (and ease the pain away from maintenance plans) – Nic Jul 14 '17 at 21:09
  • 1
    @Nic, thanks for the comment, I'll look into minionware. – T Mase Jul 14 '17 at 21:17
  • The error messages talked about tempdb space usage. It indicated that the tempdb data and log file run out of space. If you are sure that you have enough space for tempdb data and log filethen the problem probably will be that you do not have autogrow or your tempdb files reached to its size limit. Check this link: dba.stackexchange.com/questions/161462/… – CR241 Jul 14 '17 at 21:27
  • Possible duplicate of Allocate space for object 'dbo.SORT temporary run storage – CR241 Jul 14 '17 at 21:28
  • 1
    @CR241: This question is similar to the one you pointed out, and probably some of the answers might help also this case. However, IMO, the error message is sufficiently different to warrant further look. – joanolo Jul 14 '17 at 22:22
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.

  • Thanks for the suggestions. While these suggestions would work for one-offs, I'm looking for a more permanent solution. Set it and forget it. I'm going to move forward with the best practice of moving 'tempdb' it's own drive (larger than 40GB). sounds like there is no solution for a smaller 'tempdb' footprint for an on-going solution using Microsoft SQL Server's built-in DBCC CHECKDB. – T Mase Jul 18 '17 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.