Home > Uncategorized > Moving the tempdb database files

Moving the tempdb database files

Issue:

 

The tempdb files were originally on the C drive on the server. C drive is running low on disk space. As a result of that, any long transactions failed.

Solutions:

 

Need to move the tempdb database files to D drive, where there are plenty of space.

 

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

SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N’tempdb’);
GO

Step 2: Change the location of each file by using ALTER DATABASE

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘D:\MSSQL\Data\tempdb.mdf’);
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘D:\MSSQL\Data\templog.ldf’);
GO

Step 3: Stop and restart the instance of SQL Server (I have access to do that from SQL Management Studio)

Step 4: Verify the file change

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N’tempdb’);

Step 5: Delete the tempdb.mdf and templog.ldf files from the original location

Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: