SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
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
go
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

 

USE master GO ALTER DATABASE TempDB 
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)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

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

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

Working...

Post a comment


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