Rotating Header Image

Moving tempdb

Best practice dictates that tempdb should reside on it’s own physical drive(s).  There are many good reasons to do this but the two that normally get brought up and discussed are that tempdb is too big on the current drive and/or the desire to increase performance by having tempdb on different physical drives.

In order to move tempdb you first need to know the logical file name and the file path of the current location for tempdb.

something like this will get you the information you need:

 USE tempdb
GO
EXEC sp_helpfile

Assuming the default names for your files (tempdev and templog) and that you want to move tempdb from the C:\ drive to the E:\ drive the following code will do the trick:


USE MASTER
GO
ALTER DATABASE TempDB MODIFY FILE
    
(NAME = tempdev, FILENAME = 'E:\datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
    
(NAME = templog, FILENAME = 'E:\datatemplog.ldf')

Now if you wanted to get really sexy you could also put your tempdb data and log file on different physical drives but we’ll keep it like this for now.  So now you’re all done?  Not yet.  In order for the changes to take effect SQL Server needs to be stopped and restarted.

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.