Migrating TempDB

Question

Post reply

Migrating TempDB

Eric Peterson

Hall of Fame

Points: 3192
More actions
June 5, 2006 at 12:48 pm

#190816

All
The install for SQL Server 2005 puts my tempDB into the programFiles directory on the C: Drive.
My C: drive is very small, and I would like to migrate the database to or split it across two disks.
When I go into the database properties to alter the database on to the secondary drive, I get an error that says the TempDB cannot be altered.
Anyone have a way to migrate the TEMP DB without going back to re-install my server?
Eric Peteson

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 1

Execute something like the following and then restart the service

use

master

go

Alter

database tempdb modify file (name = tempdev, filename = 'd:\MSSQL\Data\tempdb.mdf')

go

Alter

database tempdb modify file (name = templog, filename = 'd:\MSSQL\Data\templog.ldf')

go

Once its restarted check the location of the files and then you can delete the old ones on c:

hth

David

Eric Peterson Hall of Fame Points: 3192 More actions · Answer 2

Thanks for the reply.

The above commands were executed. The database was shutdown and restarted.

The problem is that the Server dosent restart with the error message: The server has started and stoped. Trying it a second time I get the message "cannot open a connection to the SQL Server"

I also coppied the tempdb files to the new directory. That didnt help.

Note: I am using a test server, so I can restore the backup image.

Any other suggestions?

Eric Peterson

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 3

Tempdb should be recreated each time you start the server so you should have had to move the files but you could try opening a command prompt and navigating to

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

then sqlservr.exe -f

which should attempt to start it in a minimal configuration

This should allow a single connection run the following

SELECT name, AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

to check the location it expects to find the tempdb files then check that the directory does indeed exists and that the account that sqlserver is running under has enough rights to create the tempdb files in this location.

hth

David

MarkG Ten Centuries Points: 1237 More actions · Answer 4

Try running this after you issue your ALTER statement.. and see where SQL thinks TempDB is..

use

tempdb

go

sp_helpfile

go

hope this helps...

Mark

Eric Peterson Hall of Fame Points: 3192 More actions · Answer 5

After I change the database file location the system returns the new location.

then reboot.....

and I cannot get back in to the SQL Server

I am wondering if anyone has actually changed the location of the TempDB and this is an undocumented feature of SQL Server 2005?

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 6

To be honest it sounds like a permissions problem i have changed the location of my tempdb using exactly the

same procedure on several occasions on live and developmenet servers in both sql server 2000 & 2005

The prodecure described is also in BOL

David

Eric Peterson Hall of Fame Points: 3192 More actions · Answer 7

Thanks

It is good to know that we can change the file location. We are using a Virtual Machine for the testing, and there is always a problem or two with the configuration or permissions. Will put it on a real machine, and test again...

Thanks

Eric Peterson