SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Temporary Move

By Steve Jones,

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

 
Total article views: 7419 | Views in the last 30 days: 4
 
Related Articles
FORUM

HELP -- Lost drive with database transaction logs

master db and log on good drive -- tempdb and other transaction logs on lost drive

FORUM

TempDB

Tempdb

BLOG

SQL Server Needs Tempdb

I saw a post recently where someone noted they had moved tempdb like this: USE master; GO ALTER DAT...

FORUM

cluter server databases backup on local drive

cluter server databases backup on local drive

FORUM

Placing tempdb files on a diffrent drive.

Placing tempdb files on a diffrent drive.

Tags
administration    
configuring    
sql server 7    
 
Contribute