Tuesday, April 7, 2009

Where is your TempDB?

Eventually, I will be posting a list of my pet peeves for SQL Server. This one will make #1 or #2:

DO NOT INSTALL YOUR TEMPDB FILES ON THE ROOT!

Recently, I've started a new job. One of my first tasks has been to document the system since nothing exists to indicate what state the environment is in currently. Figuring that the best place to begin would be to understand the installation, I log into each server and do a thorough investigation of the filesystem and default locations. What I find, incredibly, is a hodge-podge of installation architectures. Incredible. The most glaring issue, by far, is the fact that on at least half of the servers, TempDB files (sometimes both data and log) are installed right on the C:\ in the installation default location.

Why should this be a problem, you say? Let me give you the short list:
  1. TempDB is responsible for a lot of processing, it can fill up FAST.
  2. A Root directory is typically only OS and the drive is often smaller than others which might be available. TempDB can fill up FAST.
  3. Configuring TempDB is often overlooked, allowing for out of control growth of both the data and the log files. TempDB can fill up FAST.
  4. When you get a call at 4am from your server telling you "there is not enough space on the disk" it doesn't make for a good start to your day.

I will happily point you to the following article on MSSQLTips.com which is a great resource for working with TempDB.

http://www.mssqltips.com/tip.asp?tip=1432

The good news is, since I was able to fix the issue quickly, I get to play the hero.

Another day in the life of a DBA!

No comments:

Post a Comment