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.”

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

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)