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
