Moving TempDB to New Drive – Interview Question of the Week #077

Midnight at 1 AM, I received a call from my customer where I have earlier worked on performance tuning project. As soon as I picked up the phone call the first statement from my friend was about TempDB and it was as follows:

“We should have followed your advice, the TempDB is full, now help us fix it.”

Moving TempDB to New Drive - Interview Question of the Week #077 tempdbmove-800x552

The matter of the fact, I exactly knew what was going on. A few days ago when I was there doing a review of their database health, I had noticed that they had a temp database on the local C Drive and it was getting filled up very quickly. I had warned them about this issue, however, I was told that they follow the principal of not fixing anything which is not broken. Honestly, I totally get that, but there are few special cases when we have to be proactive rather than reactive and keep our database in order.

Let us see a very popular interview question related to the moving temp database to new drive.

Question: How to move the TempDB to new drive when the drive is full?

Answer: Let us first see the error which is usually visible when the drive where TempDB is filled up.

Event ID: 17052
Description: The LOG FILE FOR DATABASE ‘tempdb’ IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free Up SOME LOG SPACE

There are major two reasons why TempDB needs to move from one drive to another drive

  1. Temp files grow bigger and the existing drive does not have enough space to accommodate them
  2. Moving temp database to another file group which is on a different physical drive helps to improve database disk read

Now here is a two step procedure to move the TempDB.

Step 1: Get Logical File Name

Run following script to get the logical name for the temp data and log file.

USE TempDB
GO
EXEC sp_helpfile
GO

Moving TempDB to New Drive - Interview Question of the Week #077 tempdblogical

If you have not done any customization most of the time, you will see the logical file name as tempdev for mdf data file and templog for log files.

Now use the logical name in the following query and you will be able to move the temp database to the new drive.

Step 2: Move TempDB to New Drive

Now execute following scripts which will move your temp database to new drive.

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:\datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:\datatemplog.ldf')
GO

When you execute above script you will see the message that query has run successfully. However, there will be no changes in how the temp database is configured. The location of the TempDB changed when SQL Server will restart services again. You will be able to see the changes only after the services are restarted.

Well, that’s it! We successfully changed the location of the Temp database to new drive.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server, SQL TempDB
Previous Post
What is a Master Database in SQL Server? – Interview Question of the Week #076
Next Post
How to Attach MDF Data File Without LDF Log File – Interview Question of the Week #078

Related Posts

11 Comments. Leave new

  • If the TempDB is filled up, restarting the Server will help it to free the space. But, in case of production servers, where we can’t afford to restart servers, we have to move tempDb to a diiferent physical drive in order to free the space. Please correct me if I’m wrong.

    Reply
  • Then we copy the logical file in new drive, it’s need or not

    Reply
  • You’ll still need to restart the SQL Server service for the new location of the TempDB to be activated. So either way there’ll be a downtime.

    Hence as a long term solution, just move the TempDB files to a larger disk

    Reply
  • Leonard Rutkowski
    June 28, 2016 11:49 pm

    You can’t just move the tempdb files, as suggested in the comments. You can however, move them, as documented in the article. A restart is required. However, if that is not possible, you can, just like any database, add another file, in tempdb, placing it on another drive. A restart will not then be necessary, but tempdev and templog will still be on the old drive. If you also do the alter, as described above, then the next time the server is restarted, then tempdb will be moved. Of course the new file that you added will also still be there.

    Reply
  • priyaranjan pattnayak
    June 30, 2016 1:54 pm

    Even if you move tempDB files to a different drive, you will need to restart sql services.
    If it is a prod server and your tempDB is full, you could always identify the transaction which is consuming most space in tempdb and kill it or you may add another tempDB file at a different location. That way you would not need a restart.

    Reply
  • add new file group in temp db database and select default file group option then next time all logs written in new file group in different drive. when you have down time then you can restart the SQL sever.

    Reply
    • You should have tested the answer before writing comment…

      USE [master]
      GO
      ALTER DATABASE [tempdb] ADD FILEGROUP [TempDB2]
      GO

      Error:
      Msg 1826, Level 16, State 1, Line 3
      User-defined filegroups are not allowed on “tempdb”.

      Reply
  • I need some help deleting temp DB which is default created in SQL2016, 8 of them. I need to delete temp5, 6, 7 and 8 so I will just have 4 of them. I use this query but not successful:
    use tempdb
    GO
    DBCC DROPCLEANBUFFERS
    GO
    DBCC FREEPROCCACHE
    GO
    DBCC FREESESSIONCACHE
    GO
    DBCC FREESYSTEMCACHE ( ‘ALL’)
    GO

    DBCC SHRINKDATABASE (tempdb,8)
    GO
    — Step1: First empty the data file
    USE tempdb
    GO
    DBCC SHRINKFILE (temp8, EMPTYFILE); — to empty “tempdev12” data file
    GO
    –Step2: Remove that extra data file from the database
    ALTER DATABASE tempdb
    REMOVE FILE temp8; –to delete “tempdev12” data file
    GO

    Result:

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    DBCC SHRINKDATABASE: File ID 1 of database ID 2 was skipped because the file does not have enough free space to reclaim.
    DBCC SHRINKDATABASE: File ID 3 of database ID 2 was skipped because the file does not have enough free space to reclaim.
    DBCC SHRINKDATABASE: File ID 4 of database ID 2 was skipped because the file does not have enough free space to reclaim.
    DBCC SHRINKDATABASE: File ID 5 of database ID 2 was skipped because the file does not have enough free space to reclaim.
    DBCC SHRINKDATABASE: File ID 7 of database ID 2 was skipped because the file does not have enough free space to reclaim.
    DBCC SHRINKDATABASE: File ID 8 of database ID 2 was skipped because the file does not have enough free space to reclaim.
    DBCC SHRINKDATABASE: File ID 9 of database ID 2 was skipped because the file does not have enough free space to reclaim.
    DBCC SHRINKDATABASE: File ID 2 of database ID 2 was skipped because the file does not have enough free space to reclaim.

    (1 row(s) affected)
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    DBCC SHRINKFILE: Page 9:87 could not be moved because it is a work table page.
    Msg 2555, Level 16, State 1, Line 21
    Cannot move all contents of file “temp8” to other places to complete the emptyfile operation.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    Msg 5042, Level 16, State 1, Line 24
    The file ‘temp8’ cannot be removed because it is not empty.

    Reply
  • Levi Saligue
    May 8, 2019 3:24 pm

    As always, very helpful. Thanks man!

    Reply
  • Doutor Hidrogênio
    August 22, 2020 6:32 am

    I made a change of parades from tempdb last night, after consulting this article, on a production server, which supports more than 800 people working. There was a problem, resulting from security flaws and + COM objects in the windows registry, which prevented all files from being created. To my surprise, when restarting the database instance, only one of the tempdb files had been created. I panicked, took a deep breath, checked the commands, and nothing was wrong. So I kindly asked the infra staff to reboot the server so I could try to start the sql server. In a miracle pass the BD went up and I finally managed to log in, the datafiles all created. It was one of the most terrifying moments of my life. Do not do this in a small window, schedule for weekends beforehand, SQLServer is not an exact science. Be warned, it seems to be a simple procedure but they do not know the risk they are taking, do not put their professional life at stake, schedule for a weekend.

    Reply
    • I totally agree. Always try things out first on a Development server and do all the checks and balances before you try any configuration change.

      I am glad that it worked out for you. Your story will help and motivated everyone to take enough time to do this task.

      Very happy to know all is well.

      Reply

Leave a Reply