Interview Question of the Week #027 – Move TempDB from One Drive to Another Drive When Low Disk Space

I am often invited to attend various interviews and once in a while, I see practical questions discussed in the interview. Here is one of the important interview question which is related to TempDB and how to Move TempDB from One Drive to Another Drive When Low Disk Space.

Question – “What will you do when your log file of TempDB is full?”

Answer – The best solution when Logfile of TempDB is full is to move it to a new drive. Here is more details about the same.

Here is a quick video, I have built on this subject.

Make sure that TempDB is set to auto grow and do not set a maximum size for TempDB. If the current drive is too full to allow auto grow events, then arrange a bigger drive, or add files to TempDB on another device (using ALTER DATABASE as described below and allow those files to auto grow.

Move TempDB from one drive to another drive. There are major two reasons why TempDB needs to move from a drive to another drive.
1) TempDB grows bigger and the existing drive does not have enough space.
2) Moving TempDB to another filegroup which is on a different physical drive helps to improve database disk read, as they can be read simultaneously.

Follow the direction below exactly to move the database and log from a drive (c:) to another drive (d:) and (e:).

Open Query Analyzer and connect to your server. Run this script to get the names of the files used for TempDB.

USE TempDB
GO
EXEC sp_helpfile
GO

Results will be something like:
name fileid filename filegroup size
——- —— ————————————————————– ———- ——-
tempdev 1 C:Program FilesMicrosoft SQL ServerMSSQLdatatempdb.mdf PRIMARY 16000 KB
templog 2 C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf NULL 1024 KB

Along with other information related to the database. The names of the files are usually tempdev and templog by default. These names will be used in the next statement. Run following code, to move mdf and ldf files.

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

The definition of TempDB is changed. However, no changes are made to TempDB until SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.

You can read more about this over here. You can also connect with me on LinkedIn over here.

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