How To Move TEMPDB Files to a new drive ? (Video)

Leave a comment (0) Go to comments

Moving a tempdb files is a often task for a DBA supporting a big environment. Moving a tempdb is 4 step easy process.

1. Current tempdb location, make a note of current file location using the following code.

use tempdb

go


sp_helpfile

2. Check and confirm that new location, where you want to place a tempdb files should be accessible by the SQL Server, this is very important step, specially for cluster boxes, this step ensures that you have added a new disk to sql server dependency list.  Here I am assuming, H:\MSSQL\Data\ is a new location, where you want to place tempdb files.

 xp_cmdshell 'dir H:\MSSQL\Data\'

3. Use ALTER DATABASE tempdb, TSQL statement to specify new location, where you want to place your tempdb files.

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'H:\MSSQL\Data\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'H:\MSSQL\Data\templog.ldf');

GO

4. Restart SQL Services, the new location, will be used, only after the restart

5. Delete the old files from the old directory, you made a note in Step 1

If you wanted to added multiple tempdb files to SQL Server that too in a new location, please view the following video, which will guide you, how you move tempdb to a new location and how you add multiple files to a tempdb.

Video, which covers,  1. How To Move TEMPDB Files to a new drive  2. How to add multiple files to tempdb

Related Article

How to Optimize tempdb ?

Tags : How To Move TEMPDB File,How To Move TEMPDB to a new drive, sql server How To Move TEMPDB Files to a new drive, move tempdb video,How to add multiple files to tempdb, step by step guide to move tempdb

EOF - How To Move TEMPDB Files to a new drive ? (Video), SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.