Thursday, 12 August 2010

Moving the TempDB

Got some very sexy Intel SSD drives in today for a new server, moved some databases across but also thought I'd move the temp table with it to increase the performance to the max!  Usually with databases I'd detach and then reattach the database but it's a little different for the tempDB, you need to issue the ALTER DATABASE command and tell it to issue a new location for the database.  Once you've done this though you need to restart the SQL Server instance for the changes to take effect.  The temp database has specific names for it's logical files namely tempdev and templog, so I've listed the command used below for you, enjoy speed freaks.


ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME='e:\system\tempdb.mdf')
GO
ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME='f:\system\templog.ldf')
GO
P.S. Don't forget to restart the instance

No comments:

Post a Comment