If you’ve ever had to play administrator to a SQL Server instance, you’ve probably had to deal with TempDB data or log files that have grown unexpectedly. I know a lot of folks have had to do this, because the most read post on this site is “Help! My tempdb database won’t shrink!”

Consider it a right of passage of sorts. Also, consider that it might be entirely avoidable. Let me show you.

Me Myself & I

First, I full acknowledge there is already solid advice out there about how to configure your TempdDB database. I’m not replacing any of that, but rather building on it with a few tips of my own. This should not only reduce the amount of administration required of you, but also help you react before any runaway queries cause too much damage.

OBLIGATORY DISCLAIMER: Is this going to work for all TempDBs? Definitely not, but in my experience I’ve found it to be a good starting point. Sometimes even a good ending point.

The Magic Number

Here are three simple steps to set up your TempDB:

Step 1: Provision a separate drive. Put your TempDB away from your precious user data files, as well as the other system databases. This probably isn’t your job, so ask your friends on your infrastructure team to provision and mount a T: drive or whatever of a size that you gauge appropriately. What’s appropriate? Hey, I don’t know your system – that’s why they pay you the big bucks, friend.

Step 2: Divide the total space by 10. Why 10? Well, you’re going to create 10 equally sized files: 8 data files, 1 log file, 1 empty file. 8 data files is a good starting point, and they should all be equally sized, always, with similar and synchronous autogrowth. You should only need 1 log file ever, unless you run out of space on the drive where the log file lives.

And the empty file? It’s for “delete in case of emergency” when you run out of space in the other files, so you don’t have to create another data or log file on another drive. This is going to happen someday, because every workplace has at least one analyst who executes queries that join every table in the database and then writes the results into a temporary table. It’s true.

Step 3: Tell SQL Server to pay attention. SQL Server has two built-in alerts that you can use to monitor if TempDB is being abused. These are going to tell you WHEN that analyst executes their awful query, so you can kill it before it causes too much collateral damage in tempdb. These alerts are tucked under your SQL Server Agent, in case you haven’t used them before, so set the following alerts to, uh, alert you via email or pager(?!?)

Here’s the alert for the data files. The “value” number in the screenshot is a number used to represent ~7 GB, since you’re measuring free space in KB. I’m guessing we’re measuring in KB because this alert was created in the days of pagers.

Anyhow, take the total space used by your 8 data files and then compute 20-30% of that number so you’ll get alerted when things are starting to get hairy, but before it’s all gone full sasquatch. Remember this measuring space free.

And here’s the alert for the log file, which measures space used. Why not space free like the previous alert? Because, reasons. It uses a straight percentage, so very little math is required here. Huzzah.

Potholes in My Lawn

The tempdb database is going to be used for all sorts of things. Temporary tables, work tables, row versions, stuff from spools, sorts, and cursors, and the digital artifacts of those socks you lose in the dryer. It’s ugly, I know, so don’t forget to periodically observe your favorite performance metrics because, as my lawyer advised to tell you earlier, this setup may not be enough. For various reasons you may need to split your log file from you data files, or you may need more data files, you may even need to spread those data files across multiple drives.

Hope this helps you sleep better at night.