Moving the tempdb database

Posted: January 29, 2009 in Sqlserver

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

The logical name of the database file can be found in name column.                                                            

Ex: tempdev (Will be the name master database file .mdf) and templog (Will be the name of  Transaction log file .ldf) .

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:\Mssql\tempdb.mdf’)

  go

  Alter database tempdb modify file (name = templog, filename = ‘E:\Mssql\templog.ldf’)

  go

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

 

Advertisements
Comments
  1. Michael says:

    Thanks, that fixed the problem I was having. Reports would fail as there was not enough room on the C drive to grow the tempdb enough.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s