Moving the tempdb to another drive.

Question

Moving the tempdb to another drive.

NewBornDBA2017

SSCarpal Tunnel

Points: 4832
More actions
August 11, 2017 at 7:23 am

#323057

I have 3 drives.
D: for data files which has almost 2GB of space available now.
L for logfiles
G for system DBs
TempDB is 5GB in size and it exists in D drive and I would like to move it to G drive. What are some the of the precautionary measures should I take? It is a test server, infrastructure team doesn't want to add any space and no downtime window.

Viewing 6 posts - 1 through 5 (of 5 total)

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

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 1

newdba2017 - Friday, August 11, 2017 7:23 AM
I have 3 drives.
D: for data files which has almost 2GB of space available now.
L for logfiles
G for system DBs
TempDB is 5GB in size and it exists in D drive and I would like to move it to G drive. What are some the of the precautionary measures should I take? It is a test server, infrastructure team doesn't want to add any space and no downtime window.

You don't mention how much space is available on G: drive. Is there enough to accommodate Tempdb? I would be tempted to move the system databases onto D and only have tempdb on G:.

You will need some downtime to move tempdb. Run your alter database to change the location of your tempdb files the re-start your instance and when your instance comes back up tempdb will have been moved.

Thanks

NewBornDBA2017 SSCarpal Tunnel Points: 4832 More actions · Answer 2

Restarting the service is something we can't do now. G drive has 22GB of free space available.

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 3

newdba2017 - Friday, August 11, 2017 7:37 AM
Restarting the service is something we can't do now. G drive has 22GB of free space available.

You will have to do it during your next maintenance window. As far as I'm aware there is no way you'll be able to move tempdb without restarting.

Thanks

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

newdba2017 - Friday, August 11, 2017 7:37 AM
Restarting the service is something we can't do now. G drive has 22GB of free space available.

TempDB can't be moved without a restart, so you'll have to either schedule one, or live with the current file location.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Arsh SSCertifiable Points: 6303 More actions · Answer 5

newdba2017 - Friday, August 11, 2017 7:23 AM
I have 3 drives.
D: for data files which has almost 2GB of space available now.
L for logfiles
G for system DBs
TempDB is 5GB in size and it exists in D drive and I would like to move it to G drive. What are some the of the precautionary measures should I take? It is a test server, infrastructure team doesn't want to add any space and no downtime window.

I would also suggest segregating the Data and Log files of the TempDB on separated disks for better performance , depending on what combination you can get best , out of the available options.

Arshad