SQL Server TempDB is Full !! Moving TempDB files to another drive..

Many times we get the following error in log files:
Description: The LOG FILE FOR DATABASE 'tempdb' IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free
up SOME LOG SPACE

I always follow the following conventions regarding log files:

1. I always make sure that TempDB is set to autogrow.
2. There is no maximum size limit for the TempDB.

Although we should always set the Initial size of the TempDB carefully because we do not want the TempDB to automatically add chunks frequently as it will adversely affect the performance of the database.
Now in case the current drive does not have the capacity to meet the space requirement of the TempDB database, then we can add more space to the drive or we can move the TempDB files to different drives which has the capacity to meet the space requirement.
Today I will show how we can move the files of the TempDB database to different drives.
At first lets see the current location of the TempDB files and see the other properties of the Files:
USE tempdb
sp_helpfile

I can see that TempDB files are in the E:\ drive. I will move these files in F:\TempDB\ folder. Lets first confirm the new location where we want to place these files.

I will use the following command to verify the new location:

xp_cmdshell ‘dir f:\tempdb‘

Till now everything looks good to move the files to the new location. We cannot change the location of the files through SSMS. We have run the following statement:

ALTER DATABASE tempdb
MODIFY FILE(NAME=tempdev, FILENAME=‘F:\Tempdb\tempdb.mdf’);
GO

ALTER DATABASE tempdb
MODIFY FILE(NAME=templog, FILENAME=‘F:\Tempdb\templog.ldf’);
GO

It will show us the following message:

So we can see the message that the new path will be used upon restarting the database. So we need to restart the server in order to use the new location for the TempDB database.

After restarting the server, lets check the location of the files of TempDB:

sp_helpfile

So we can see that we have successfully changed the location of the files.