Collapse All Expand All

Moving The Tempdb Database

You can move tempdb files by using the ALTER DATABASE statement.

1. Determine the logical file names for the tempdb database by using sp_helpfile as follows:
   use tempdb
   go
   sp_helpfile
   go
   The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.

2. Use the ALTER DATABASE statement, specifying the logical file name as follows:
   use master
   go
   Alter database tempdb modify file (name = tempdev, filename = ‘E:\Sqldata\tempdb.mdf’)
   go
   Alter database tempdb modify file (name = templog, filename = ‘E:\Sqldata\templog.ldf’)
   go
   You should receive the following messages that confirm the change:
   Message 1
   File ‘tempdev’ modified in sysaltfiles. Delete old file after restarting SQL Server.
   Message 2
   File ‘templog’ modified in sysaltfiles. Delete old file after restarting SQL Server.

    

3. Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.
4. Stop and then restart SQL Server.

 

 

---

This website and its content is copyright of ScrapDragon, LLC - © ScrapDragon, LLC 2018. All rights reserved. Any redistribution or reproduction of part or all of the contents in any form is prohibited other than the following: You may copy the content to individual third parties for their personal use, but only if you acknowledge the website as the source of the material. You may not, except with our express written permission, distribute or commercially exploit the content. Nor may you transmit it or store it in any other website or other form of electronic retrieval system.