Moving TempDB data and log file locations

I have a test database server, at the time the server was setup it had minimal drives and space on those drives was small. It was setup on the stingy side because it is a testing server. I knew I would have problems with it if I was going to do any work on the majority of my databases due to the size limitations.

Recently, I had additional drives and space added so I wanted to move some things around a bit. This is a short post on how to move data and log files for your database. We are going to be talking about the TempDB today but this code and method works for all your databases.

Find File and Name Location

Before we move any files around we need to make sure we know where they are. We have standard configurations but some of the servers were setup prior to the establishment of standards, so the data files can be almost anywhere. I know, it’s not the best environment but as servers are modified I try and get things closer to our standards.

Open a query window in SQL Server Management Studio (SSMS) and type or copy the following code into the window and execute.

USE [tempdb]
GO

EXEC sp_helpfile

Your results will be like this. Notice the files are located on the D:\TempDB drive and folder.

findFileNamesAndLocations

Move The Files

Now that we know where the files are we will move them to the T:\TempDB drive by executing the following command.

MODIFY FILE ( NAME = logical_file_name, FILENAME = ‘ new_path/os_file_name ‘ )

This is the code I used to my the files.

ALTER_DATABASE_MODIFY_FILE

For this demo and to keep things simple I moved the log file as well. Ideally, you want to place the log file with other log files on their own drive separated from data files.

Restart SQL Server

Now, we want to restart SQL Server

restart SQL Server

Now we want to confirm the files are where we want them to be.

USE [tempdb]
GO

EXEC sp_helpfile

ALTER_DATABASE_MODIFY_FILE_results2

OK, so the files are now moved to where we wanted them, our work here is done.

As you can see this is very handy and a really good thing to know, especially if you cover other projects that have difficulty managing their disc space. Not that something like that would go on in my shop or yours for that matter. J

If you want to read up more on ALTER Database T-SQL, here is the link to MSDN

http://msdn.microsoft.com/en-us/library/bb522469.aspx

Cheers!

Advertisements

One comment

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