DBCC CheckDB causes TempDB grows massively ?

Question

DBCC CheckDB causes TempDB grows massively ?

WhiteLotus

SSChampion

Points: 12122
More actions
January 17, 2020 at 3:36 am

#3716187

Hi
Currently we are running DBCC CheckDB script in our server using SQL JOb every week
Concern is it takes 3 hours and affect Tempdb Size grows massively after 4 times running in a row ( first time was successful and the rest is failed )
I am thinking to adjust the parameter so it can run faster and minimize impact of Tempdb size
example :
Running DBCC CheckDB with Physical Only
Please let me know your thoughts
Your feedback is much appreciated

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Nice Marmot SSCrazy Points: 2278 More actions · Answer 1

If I recall correctly, DBCC CHECKDB uses a snapshot, and pages that change while it is running are copied to tempdb. If you are seeing abnormal growth of tempdb while DBCC CHECKDB is running, that is probably due to the volume of changes being made to the database being checked.

DBCC CHECKDB really needs to run when there is little or no activity on the database, certainly no batch jobs or ETL jobs.

HH

Akimotos Newbie Points: 1 More actions · Answer 2

check out this article by Paul Randall: https://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-complete-description-of-all-checkdb-stages/

Also very helpfull is the pluralsight https://app.pluralsight.com/library/courses/sqlserver-database-corruption/description

(i hope i dont break any rules by posting those two links that i consider mandatory for every serious DBA.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

There's another strong and unavoidable issue with TempDB. They've made it so that it follows the same rules as if you invoked Trace Flag 1117 (equal file growth of all files in the file group) and it cannot be disabled for TempDB even on a temporary basis. If you normally have, say, 8 files of 2GB each and the drive is limited to only, say, 100GB, it only takes 1 operation that grows 1 file to a little more than 12.5GB and BOOM!!! You're system is out of space and whatever you were doing rollsback, etc, etc.

I do sometimes wish that MS would stop trying to help and, at the very least, provide an override for such help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tmisganaw Old Hand Points: 336 More actions · Answer 4

You can use this as an alternative, DBCC CheckDB PHYSICAL_ONLY; it is a limited check but doesn't use that much TempDb

WhiteLotus SSChampion Points: 12122 More actions · Answer 5

WhiteLotus

SSChampion

Points: 12122

January 21, 2020 at 10:22 pm

#3717190

Thanks so much for the advice All !