Press "Enter" to skip to content

Managing tempdb Activity

Aaron Bertrand has built a solution:

Managing tempdb growth tends to be a very reactive exercise. We size tempdb using a predictive ruleset based on expected workload, database size, concurrency, and other imperfect factors. Then someone does something silly and fills the T: drive. If we catch it in time, we may be able to kill the offending session or add space to prevent the workload from dying. Then we tell them not to do it again; but they will, of course, so we expand the drive again and hope this time it’s big enough. But it rarely is. It’s like a baseball game that’s tied in the 5,662nd inning. Each team keeps scoring a single run in every new inning; the game will never end.

Our existing “drive full” alarms start going bonkers as soon as a drive is more than 90% full. We have a dedicated drive for tempdb data, and we pre-allocate the data files to just under 90% of the available space. This prevents any growth events while they’re using tempdb within the bounds of its current size. But when we fill the space already allocated to the data files, they grow. After one or two growths, we have crossed the threshold for drive space alerts, and it probably won’t be long before the volume fills and the workload grinds to a halt.

Click through for the solution scripts and Aaron’s explanation of it all.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.