• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1016
  • Last Modified:

Moving SQL 2005 Tempdb

Hello,
I need to move the tempd from one device/disk to another.
Will the below commands work:
USE master
GO
ALTER DATABASE tempdb
      MODIFY FILE (NAME = tempdev, FILENAME = 'C:\tempdb2005.mdf')
GO
ALTER DATABASE tempdb
      MODIFY FILE (NAME = templog, FILENAME = 'C:\tempdb2005.ldf')
GO
and do i need to do anyting else i.e. detach the db first before moving ?
Thanks
0
BayernLB
Asked:
BayernLB
  • 2
1 Solution
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
If you come across following errors in log file, please follow the direction below.
Source: MSSQLSERVER
Event ID: 17052
Description: The LOG FILE FOR DATABASE tempdb IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free
up SOME LOG SPACE

Make sure that TempDB is set to autogrow and do not set a maximum size for TempDB. If the current drive is too full to allow autogrow events, then arrange a bigger drive, or add files to TempDB on another device (using ALTER DATABASE as described below and allow those files to autogrow.

Move TempDB from one drive to another drive. There are major two reasons why TempDB needs to move from one drive to other drive.
1) TempDB grows big and the existing drive does not have enough space.
2) Moving TempDB to another file group which is on different physical drive helps to improve database disk read, as they can be read simultaneously.

Follow direction below exactly to move database and log from one drive (c:) to another drive (d:) and (e:).

Open Query Analyzer and connect to your server. Run this script to get the names of the files used for TempDB.
USE TempDB
GO
EXEC sp_helpfile
GO

Results will be something like:
name fileid filename filegroup size
-   - -
tempdev 1 C:Program FilesMicrosoft SQL ServerMSSQLdatatempdb.mdf PRIMARY 16000 KB
templog 2 C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf NULL 1024 KB
along with other information related to the database. The names of the files are usually tempdev and demplog by default. These names will be used in next statement. Run following code, to move mdf and ldf files.
USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = d:datatempdb.mdf)
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = e:datatemplog.ldf)
GO

The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.

Orginally from : http://blog.sqlauthority.com/2007/04/01/sql-server-tempdb-is-full-move-tempdb-from-one-drive-to-another-drive/
0
 
BayernLBAuthor Commented:
I am not coming across that error but i am getting these errors:
Operating system error 112(there is not enough space on disk) encounted
and
Version store is full. New version(s) could not be added. A transaction that needs to access the version
store may be rolled back. Please refer to BOL on hoe to configure tempdb for versioning

Shall i still move the tempdb on to a disk with more space ? and can i use the above commands ?

Thanks
0
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
Yes need to detach the database first.
Ex: moving db mydb
Detach:
use master
go
sp_detach_db 'mydb'
go
Next, copy the data files and the log files from the current location to the new location.
Re-attach the database. Point to the files in the new location as follows.
Re-Attach
use master
go
sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
go
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now