4

DBCC CHECKDB is failing on a particular database because of

Could not allocate space for object 'dbo.SORT temporary run storage: 143479689576448' 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.

Tempdb is currently 5GB. The database in question is 56GB. The biggest table in that db is 15GB.

How big shall I make tempdb?

Thanks

Improve this question
5
  • Sounds like your Tempdb is big enough but something else is filling it up. How much free space does it have before you run your CHECKDB?
    – Jhunter1
    Commented Oct 9, 2019 at 15:24
  • At the moment it has 4897.16 MB of 5487.44 MB available - I'm not sure how to establish how much space it has just before CHECKDB runs? (Saturday mornings)
    – jack
    Commented Oct 9, 2019 at 15:27
  • Add another step in the SQL Agent job (assuming you're using it) to first check how much free space there is in the file. If less than say 4 gb throw an error and dont run checkdb? Might give you a starting point
    – Jhunter1
    Commented Oct 9, 2019 at 15:35
  • So i ran it manually with the 4.9GB free currently in tempdb and still get the same error Msg 1105, Level 17, State 2, Line 1 Could not allocate space for object 'dbo.SORT temporary run storage: 143493608046592' 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.
    – jack
    Commented Oct 9, 2019 at 16:16
  • 1
    These might help. Minimizing the impact of DBCC CHECKDB : DOs and DON'Ts by Aaron Bertrand, How to be sure that tempdb size is good to run a DBCC CHECKDB? by Stephane Haby and How does DBCC CHECKDB WITH ESTIMATEONLY work? by Paul Randal.
    – SqlWorldWide
    Commented Oct 9, 2019 at 19:06

2 Answers 2

Reset to default
4

You can use the "WITH ESTIMATEONLY" with your dbcc command.

DBCC CHECKDB WITH ESTIMATEONLY

This should give you an aprox. space in tempdb to run it. .

Improve this answer
1
  • The results of this tell me in the 0.02 GB range - which tempdb is already much bigger than (5GB)
    – jack
    Commented Oct 9, 2019 at 15:30
4

TempDB should be sized based on the size of the drive it's on (and it should be on its own drive). Generally speaking you should have one TempDB file per CPU core (up to 8) and one TempDB_Log file. So...divide total space on the drive by (number of CPU cores + 1). The resulting number is how large each TempDB file should be, and the size of the log file.

For example let's say your TempDB drive is 60 gigs and your SQL server has 8 cores. 60/(8+1) = 6.66 gigs. You need to configure 8 TempDB files of ~6800MB and one TempDB_Log file of ~6800 MB, and set Autogrowth to off.

You'll also probably want to tell the server admin that the drive is going to show as 99% full so they can disable disk space alerts.

For more in depth info check out Brent Ozar's writeup.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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