• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

How to move tempdb

November 29, 2011 by Andy Hayes 2 Comments

How to move tempdb ? So in this post, I show you what is involved to do this and what you shouldn’t try and do.

This is how to move tempdb

You will need to run run some T-SQL and restart the sql server service to complete the operation.

The logical file name values will need to be obtained and there are a couple of ways to do that.

You can either view the logical file names by accessing the database properties and clicking on “Files” or you can run a script. See screen shots below.

how to move tempdb

You can also run this T-SQL:
[sourcecode language=’sql’]SELECT name, physical_name AS Location
FROM sys.master_files
WHERE database_id = DB_ID(N’tempdb’);
GO[/sourcecode]
On my machine, the results were shown in Management Studio as below and here I am interested in the values in the “name” column. You will need these for the next script.

how to move tempdb

Once you have the logical file names for tempdb, you can run the following script after you have edited it with suitable values for your system. In this example, I am going to set the new path to be another folder on the C: drive of my test server but it is likely that you would be moving tempdb to another drive if this were a real scenario. Whatever path you choose, ensure that the account which the sql server service is running under has permission to read and write to it.
[sourcecode language=’sql’]USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\TEMPDB\Tempdb.mdf’);
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\TEMPDB\Tempdb.ldf’);
GO[/sourcecode]
Note that the destination path must exist otherwise the script will fail to run.

If the script completes successfully, you will see the following message:

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.

Now restart your sql server service.

Move tempdb – don’ts

Don’t try and move tempdb using a backup and restore method as you will receive an error

Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Don’t try and detach tempdb using sp_detach_db @dbname=’tempdb’ as you will again see an error

Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.

I hope you have found this information useful on how to move tempdb and if you would like to watch a video on this topic, you can do so right here.

Filed Under: All Articles, SQL Server Administration Tagged With: tempdb, video

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Comments

  1. Kaspar Kaegi says

    November 14, 2012 at 8:41 pm

    Thanks for this instruction. Between line 7 and 8 you need also Modify File….

    Reply
    • Andy Hayes says

      November 14, 2012 at 9:33 pm

      Thank you for spotting that Kaspar. I have now corrected that typo 🙂

      All the best

      Andy

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CAPTCHA
Refresh

*

Primary Sidebar

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©