SQL Server – Tempdb move files to another drive

07 December,2012 by Jack Vamvas

There is a requirement to move a TempDB file from one drive to another. There may be many different reasons – such as the Tempb is FULL or it is a scheduled change.   Moving a TempDB file requires executing the ALTER DATABASE command and a SQL Server Restart.

View the current location of the TempDB files by :

use tempdb
select fileid,name,[filename] from sys.sysfiles

--the output is something like
--fileid	name	filename
--1	tempdev	G:\MSSQLSERVER\MSSQL$INST1\Data\tempdb.mdf
--2	templog	F:\MSSQLSERVER\MSSQL$INST1\Data\templog.ldf
--3	tempdev1	E:\MSSQLSERVER\MSSQL$INST1\Data\tempdev1.ndf


You’ll notice in the output three files, the “tempdev1” file was added as an extra file earlier on. All I want to complete is moving the”tempdev1” file, but the same principle applies to the tempdev and templog file


MODIFY FILE (NAME = tempdev1, FILENAME = 'J:\tempdev1.mdf') 
--then a SQL Server restart

 Read More

TempDB performance and strategy checklist - SQL Server DBA

SQL Server – List current tempdb statements - SQL Server DBA

Shrink tempdb without sql server restart - SQL Server DBA

SQL Server – tempdb and solid state drives - SQL Server DBA

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server – Tempdb move files to another drive

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer