Moving the tempdb database | SansSQL

Tuesday, December 28, 2010

Moving the tempdb database

There are cases when you might want to move tempdb database from an existing drive to a new drive.
  1. When the drive is full and you are in a situation where you cannot extend that drive.
  2. Move tempdb to a separate drive to increase its performance.
This is a simple process and cannot be done by detaching and attaching the database, as we cannot attach or detach a system database.
Also we need to restart the SQL services.

Here is the process how we can move the tempdb to a new location.
  1. First get the list of tempdb files by using this query
  2. select name,physical_name from sys.master_files where DB_NAME(database_id)='tempdb'
  3. Then for each tempdb file that you need to move, execute statements like below
  4. Alter Database tempdb modify file (NAME = 'tempdev' ,
    FILENAME = 'Drive:\Path\tempdb.mdf') -- Mention the new location
    Alter Database tempdb modify file (NAME = 'templog' , FILENAME = 'Drive:\Path\templog.ldf') -- Mention the new location
  5. Stop SQL Services
  6. Start SQL Services
  7. Verify the new Location
    select name,physical_name from sys.master_files where DB_NAME(database_id)='tempdb'

Ads