How to move TempDB in SQLServer?

If the TempDB grows fast and does not have have enough space to grow then the best option is move the TempDB to another physical drive, which will help to improve database performance.

You will receive below error messages, if tempdb is full

Source: MSSQLSERVER
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

Server: Msg 1101, Level 17, State 10, Line 1
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.

Follow the below steps to move the tempdb to new location.

Step 1: Open a “New Query” Window and run the below command to get the path and names of the TempDB.

USE TempDB
GO
EXEC sp_helpfile
GO

Or

SELECT name, physical_name FROM sys.master_files
WHERE database_id = DB_ID('TempDB');

Step 2: In the following query Replace “NewPath” with new drive path and run the command to change the LDF and MDF file path.

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '}\templog.ldf');
GO

The tempDB definition has been changed, but no changes are made to until you restart SQLServer. Please stop and restart SQL Server and

Step 3: Stop and Start the SQLServer

Control Panel -- > Administrative Tools --> Services --> Right click on SQLServer (MSSQLSERVER) --> Restart

or

SQL Server Configuration Manager --> SQLServer 2008 Services --> Right Click on SQLServer (MSSQLSERVER) --> Restart

Now TempDB files created in new drive.

Step 4: Check the TempDB new file location patch

SELECT name, physical_name FROM sys.master_files
WHERE database_id = DB_ID('TempDB');

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com