TempDB requirement for DBCC CheckDB

Question

TempDB requirement for DBCC CheckDB

Devendra Yadav 31

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 -

When I run - DBCC CHECKDB with No_infomsgs, all_errormsgs, Physical_only
--- This executes successfully and with no errors. (Happy to see this :) )
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

Shashank Singh 6,211

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

Devendra Yadav 31 Reputation points

2020-12-03T13:40:32.057+00:00
Hi Shashank,

Thank you for your advice :)

The SQL server is patched till SQL 2016 SP2 CU13. So patches reason is ruled out.

The Checks are running in DEV server and no jobs or applications are running at that time, the SQL server is totally idle, i have checked for other sessions as well. No other session apart from mine and DBCC CHECKDB are found.

he tempdb drive is of 48GB out of which 40GB is allocated to 4 datafiles. At first i had run into Tempdb full situation. And i am aware that it needs more disk space, but the estimate is way high and not justifiable. Need suggestion and solutions on this?

The database mainly holds 3-4 tables out of which one is large table with many columns of varchar(max). The table size is more than 600 GB including a clustered index and 2 non clustered indexes.
Shashank Singh 6,211 Reputation points

2020-12-03T13:48:23.44+00:00

48 GB tempdb size for 1 TB database would always lead you in out of space message when running checkdb. Since you said it is dev then I would not suggest you to add more space as you might not get approval.

but the estimate is way high and not justifiable. Need suggestion and solutions on this?

I dont think anyone can fix that but MS, its basically SQL Server code giving you that. I can only direct you to how-does-dbcc-checkdb-with-estimateonly-work. If I were you I would follow a workaround as suggested by Paul (quoted in my answer). Let us say if the estimate would be 500GB which is totally fine, would you add that much space ? I would suggest not to spend time on this and find alternate solution.
Devendra Yadav 31 Reputation points

2020-12-03T13:56:52.55+00:00

Thanks again for your response.

Yes i am going through the Paul Randal's suggestion. Let me see it that helps.

Any other suggestions are welcome

Answer 2

CathyJi-MSFT 20,776 Microsoft Vendor

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.

Devendra Yadav 31 Reputation points

2020-12-04T08:26:15.78+00:00

Hi @CathyJi-MSFT

Thanks for your response.

The database has 8-10 tables of which only one table is about 700GB and rest all are in MB's .

And this quote “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.”
--I am unable to understand how this can be practically done? Could you please share an example how pages can be checked using DBCC CHECKDB. I am curious to know this.

And on a happy note to everyone supporting on this post --

I have been able to sort this out by expanding the disk space by 40 GB more i.e. total 80GB and when the DBCC CHECKDB has been running, I evaluated that it used around 64 GB of space and later they got released. And the check was also successful.

I am still not convinced by the estimate suggested by SQL Server and want to know if such a situation happens again, what should be the way forward.

Thank you @Shashank Singh
Shashank Singh 6,211 Reputation points

2020-12-04T10:59:18.69+00:00

@devendraYadav2197 Glad your issue is resolved upvoted it. If you ask me the estimate has been buggy. It was fixed in SQL Server 2008 r2, 2012 and may be in other version. If you like to raise feedback to MS you are welcome I would personally bring this thread to MS folks. Its incredible that checkdb estimates 718 GB and settles with 64 GB. Very greedy :)
Devendra Yadav 31 Reputation points

2020-12-04T16:19:16.44+00:00

Thank you @Shashank Singh

I'll raise feedback.
And Thanks you again for taking this personally to MS people.

Regards,
Devendra Yadav
CathyJi-MSFT 20,776 Reputation points Microsoft Vendor

2020-12-07T10:02:49.54+00:00

Hi @Devendra Yadav ,

Thanks for your reply. If the responses from us helped, do "Accept Answer". By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Cathy

TempDB requirement for DBCC CheckDB

1 additional answer

Activity