Disk sizing considerations for 'tempdb' - Growing substantially two times a year

I have an HR software on which an employee of the HR department runs long and complex analyses and calculations about two times in a year. Because of this the tempdb is growing to 500GB and even more.

What is a good disk sizing solution? Because the rest of the year tempdb is not so big.

Details

• The SQL Server version and edition of relevance are 2017 Standard.

• tempdb and databases are on the same partition D: and Log files are on a different partion E:

• The HR database itself has around 78GB.

The "real" answer is: work with HR and/or their software vendor to fix or improve their twice-annual jobs so they don't do that.

Assuming that's not possible, you're left with two three lousy options:

• Leave tempdb huge all year long.

• Leave enough empty space on disk so that tempdb can grow huge, then shrink it back down immediately afterwards.

• Coordinate with systems to add disk space just before processing, then shrink tempdb afterwards and recover the disk space.

1 and 2 are pretty much functionally identical, although I'd tend toward 2 (just in case the vendor got its act together sometime in the last 6 months and fixed their processing job without you knowing it).

Exactly how to undertake #3 would depend on lots of technical details that will vary by your environment. One method I'd consider would be:

• Before processing, add a large temporary drive (say z:) to the server

• Do an ALTER DATABASE tempdb MODIFY FILE for each tempdb file, specifying both the new location on z: and a larger file size

• Restart SQL for that change to take effect (SQL re-creates tempdb every time it restarts)

• Let tempdb use up as much space as it needs on z: during processing

• After processing is done, re-execute the ALTER DATABASE tempdb MODIFY FILE to redirect tempdb back to the original drive (and the original smaller size)

• Restart SQL again for the change to take effect

• Drop/remove the now-empty disk

In our environment, this is preferable because (with our particular configuration of SAN/virtualization), it happens to be vastly easier to add and later remove a drive than it is to expand an existing drive and (especially) to shrink an existing drive. (If I remember right, shrinking an existing drive literally requires presenting a whole new disk and migrating data). But your mileage may vary depending on your configuration.

StackExchange Database Administrators Q#207753, answer score: 6