question

vivekchandra09 avatar image
vivekchandra09 asked

need to move tempdb from c drive to g drive

I need to move tempdb currently residing on c drive. I need to move it to g drive. How do I do it (command wise). I have a ticket open and can take outage.
tempdbcommands
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

the g drive is named tempdb now
0 Likes 0 ·
OK I got it done. Thanks everyone.
0 Likes 0 ·
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
0 Likes 0 ·
@SQLShark avatar image
@SQLShark answered
Hi there, ALTER DATABASE tempdb MODIFY FILE (name = tempdev, filename = 'G:\NewTempDB\tempdb.mdf') go ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'G:\NewTempDB\templog.ldf') go If you need the logical names for the tempdb files run sp_helpfile You will need to alter where the mdf and ldf files are going. Once this has been completed you will need to restart SQL Server
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ALTER DATABASE tempdb MODIFY FILE (name = tempdb, filename = 'G:\NewTempDB\tempdb.mdf') go ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'G:\NewTempDB\templog.ldf') go this is what I am going to run. Wish me luck.
0 Likes 0 ·
Let us know how you get on.
0 Likes 0 ·
Msg 5041, Level 16, State 1, Line 1 MODIFY FILE failed. File 'tempdb' does not exist. Msg 5121, Level 16, State 1, Line 2 The path specified by "G:\NewTempDB\templog.ldf" is not in a valid directory.
0 Likes 0 ·
sp_helpfile results are as below: name fileid filename filegroup size maxsize growth usage tempdev 1 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf PRIMARY 107734976 KB Unlimited 10% data only templog 2 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf NULL 2241984 KB Unlimited 10% log only
0 Likes 0 ·
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started. The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.
0 Likes 0 ·
Show more comments
raadee avatar image
raadee answered
Lets start from the beginning. 1. You need to create the G:\NewTempDB\ directory\folder (or whatever name you choose to move the files too). 2. After you created NewTempDB folder, then you need to right click -> properties -> security and add your SQL Service account, set full control permissions for it. 3. Now you can run the alter database command, make sure that the files end up in the created folder in the command. 4. Restart the SQL Service 5. Delete the old tempdb files on the C:\ drive **NOTE** Up to SQL 2008 R2 there is a local computer group named "SQLServerMSSQLUser$YOURSERVERNAME$MSSQLSERVER" (default instance), your SQL Service account is a member of this group. SQL uses this group for folder permissions, so the best choice is to add folder permissions to this group instead of the actual service account. The advantage of using this group is if you change your service account then you won't have to add permissions on the NewTempDB folder again for the new account.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.