TempDB requirement for DBCC CheckDB

Question

Dear Experts,

I have a database of 1 TB approx on SQL Server 2016 Enterprise Edition.
The Server has 4 CPU's 128GB RAM.
The TempDB configured is 4 datafiles of 10GB each (no. of CPU's) and a logfile.
I have configured to run Integrity Check (DBCC CHECKDB) on the database. This has two cases -

1. When I run - DBCC CHECKDB with No_infomsgs, all_errormsgs, Physical_only
   --- This executes successfully and with no errors. (Happy to see this :) )
2. However, when I run - DBCC CHECKDB with No_infomsgs, all_errormsgs
   --- This throws an error stating below
   "Msg 1105, Level 17, State 2, Line 1
   Could not allocate space for object 'dbo.SORT temporary run storage: 140757349706800' 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."

Now to fix this I ran - DBCC CHECKDB with No_infomsgs, all_errormsgs, ESTIMATEONLY
This is suggesting this -- "Estimated TEMPDB space (in KB) needed for CHECKDB on database MyDB= 718102059."
which is about 718GB and i am not able to take a decision on this.

Could you guys suggest if you ever came across such a situation.

Regards,
Devendra Yadav

Answer 1

First make sure the SQL Server 2016 is patched to latest Service pack and CU to rule out any possibility of wrong estimation. Next is how much space have you allocated to tempdb ? As i see if you want to run complete checkdb in one go you have to add space in tempdb. Plus make sure you run checkdb when no other task is running. Its difficult for Checkdb to store 1TB check in 128 GB RAM so it will spill this to tempdb. But I must add that 718 GB seems on higher side.

I suggestion is to follow what Paul Randal has mentioned in CHECKDB (Part 6): Consistency checking options for a VLDB, see below

Run a bi-weekly DBCC CHECKALLOC

Figure out your largest tables (by number of pages) and split the total number into 7 buckets, such that there are a roughly equal number of database pages in each bucket.

Take all the remaining tables in the database and divide them equally between the 7 buckets (using number of pages again)

On Sunday:

Run a DBCC CHECKALLOC

Run a DBCC CHECKCATALOG

Run a DBCC CHECKTABLE on each table in the first bucket

On Monday, Tuesday, Wednesday:

Run a DBCC CHECKTABLE on each table in the 2nd, 3rd, 4th buckets, respectively

On Thursday:

Run a DBCC CHECKALLOC

Run a DBCC CHECKTABLE on each table in the 5th bucket

On Friday and Saturday:

Run a DBCC CHECKTABLE on each table in the 6th and 7th buckets, respectively

You ca run physical_only one week and then other week you can follow above procedure. Just list out your large tables and run dbcc checktable for them so that your chunk of data is secure.

PS: You need to be creative here. Adding more RAM with some tempdb space will definitely solves this issue

Answer 2

Hi @Devendra Yadav ,

Agree with Shashank, split Check DB option is good for VLDBs. It has two methods. 1. File and filegroup checkDB 2. Default, one MDF filegroup checkDB.

Method 1: File and file group, you can run “DBCC CHECKFILEGROUP”. It is easy one and you need to make sure the size of the files needs to run each day. If the size is not same for all the files, then plan it to combine and run accordingly.

Method 2: Single file VLDBs, use a split checkDB.

“Figure out your largest tables (by number of pages) and split the total number into 7 buckets, such that there are a roughly equal number of database pages in each bucket.”

Refer to the blog VLDB very large database DBCC checkDB

For TEMPDB space needed for CHECKDB, please check if below blog could help you.

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

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.