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.
|
