Appropriate tempdb size?

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

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.

StackExchange Database Administrators Q#250680, answer score: 4