1

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

  • 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 Oct 9 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 Oct 9 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 Oct 9 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 Oct 9 at 16:16
  • 1
2

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. .

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

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.

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.