SQL Server Tips

How to move SQL Server transaction log files to different location

One of our customer recently purchase new hard disk for their database server, and need to place all SQL Server transaction log files to that new hard disk. As far as i know, SQL Server transaction log file records every transaction that was executed in a database. And i think to placing transaction log files in separated location will allow disk to work optimally. These are the steps to move SQL Server transaction log files :

Determine current database files location and transaction log file location and then record the location, it will be used later when I re attached the database using the following command :

USE <databasename>
GO
EXEC sp_helpfile
GO

The above command will return result as the following sample :

sp_helpfile

Take a note for primary database file location and log file location. Then ensure that no one connect to database, or if there still any user connected, you can kill the connection using procedure on the other article, then we are going to detach the database. From SQL Server Management Studio Object Explorer, expand Databases, right click database name you want to detach, point to Tasks and click Detach

On Detach Database page, ensure that the status is Ready, then click OK to continue Detach process. If the status is Not Ready, you can found the reason under the Message column.

detach-db-window

Once Detach process has completed, move the transaction log file from the old location to the new location using windows explorer. Ensure that directory permission on new location is same with permission on the old location. Then go to SQL Server Management Studio, from object explorer, right click on Databases and click Attach.

attach-menu

On Attach Database page, click Add button, and navigate to database file location, select Database file name and click OK

locate-database

When return to Attach Database page, you will notice a message that log file was not found. Click browse button (…) then navigate to the new log file location, select the log file and click OK

attach-db-page

After select the log file, click OK button again to Attach the database. At this point, this database  should be back online. Verify the database file and transaction log file location using the above SQL command.

Advertisements

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