Database Knowledge Base / MS SQL Server / 2005 /
Add comment
Name:
Email:
* Comment:
(Use BBcode - No HTML)
code
* Confirmation code:   Write the characters in the image above exactly as you see it


what is the method to change location of tempdb?

1. Determine the logical file names of the tempdb database and their current location on the disk.

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');
GO

2 Change the location of each file by using

ALTER DATABASE. 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

3. Stop and restart SQL Server.

4. Verify the change
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');

Reference: Microsoft SQL Server Books Online




RSS