A Temporary Move
Introduction
Recently I had to move a bunch of system databases on a server to have it conform to our standards. I last wrote about moving master to another drive and moving msdb to another drive. This time I am finishing the series with the last system database to move, tempdb.
Why would you move any system database? Well, I mentioned reasons in the other articles, but for tempdb the biggest reason is usually performance. By separating out tempdb from other databases, you can increase the throughput, especially if it moves to a separate physical drive.
The process turns out to be even simpler than moving msbd or master. Let's walk through this simple procedure. The first step is to open Query Analyzer and connect to your server. Once connected, you can run this script to get the names of the files used for tempdb.
use tempdb
go
sp_helpfile
go
You should see something like:
name fileid filename filegroup size
------- ------ -------------------------------------------------------------- ---------- -------
tempdev 1 C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf PRIMARY 8192 KB
templog 2 C:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf NULL 768 KB
along with other information. Note the names of the files, usually tempdev and demplog by default. You need these names in the next statement, which will actually move the files. Suppose I wanted tempdb to move to a brand to t: drive with it's log on the u: drive. I could run the following:
use master
go
Alter database tempdb modify file (name = tempdev, filename = 't:\data\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'u:\data\templog.ldf')
go
At this point, the definition of tempdb is changed. However, since the database is rebuilt everytime SQL Server starts, there are no files to move. You stop and restart SQL Server and it will create tempdb in your new locations.
Read Part I -
Move Your Master or part II -
Moving MSDB
Conclusions
Once again, an easy process. Simple, but something that would require some research. Fortuneately, I've done that for you and hopefully this resource will be handy when you need it. If you have any comments or questions, please feel free to note them below using the "Your Opinion" button.
Steve Jones
©dkRanch.net November 2002
Return to Steve Jones Home
