Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Moving TEMPDB Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 4:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 4:03 PM
Points: 43, Visits: 138
Hi all,

I have a default installation and I need to move just the TEMPDB database from C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data to another partition (E:\) because this database is too big and I don't have enogh space in C:\.

I did a backup database but I don't know What other considerations should I have

Using this code:
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
go



Thanks,




Post #1444634
Posted Friday, April 19, 2013 4:46 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:44 PM
Points: 21,209, Visits: 14,899
Other considerations besides just moving the files (code should be fine that you provided)

Consider the size of the files
Consider more files than just the default 1 data file
Consider the growth settings for the files

Monitor for activity and adjust as necessary if contention in tempdb arises.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1444641
Posted Friday, April 19, 2013 5:03 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 5,974, Visits: 12,877
ensure the sql service account has full control permissions on the E:\sqldata directory.

You will need to restart the instance for the change to take effect.

Delete the old files after successful move.

You cannot backup tempdb so not sure what you mean by that.


---------------------------------------------------------------------

Post #1444649
Posted Tuesday, April 23, 2013 8:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 4:03 PM
Points: 43, Visits: 138
Thanks for sharing your experiences,

I will try to apply it.
Post #1445461
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse