Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Move SQL Server transaction log files to a different location via TSQL and SSMS

MSSQLTips author Atif Shehzad By:   |   Read Comments (6)   |   Related Tips: More > Database Administration

Problem
I know that moving the log file of a production SQL Server database to separate physical drive is a best practice to optimize the I/O performance.  Recently, we have added a physical drive to our SQL Server. Based on this change, I am planning on moving the SQL Server log file of my production database on this drive. I am curious about how it will optimize the performance.  What are some of the considerations I should take into account and how can I move the SQL Server database log file to a separate physical location?

Solution
All SQL Server databases have at least one primary database file and one transaction log file.  The transaction log file records every data change and DML transaction that was executed in the database.  Writing to the transaction log file is sequential in nature as compared to the database files which are typically random I/O.  As such, placing the log file on separate physical disk from database will allow the disk to work in sequential manner and perform optimally.  To move to this configuration, it will be necessary to detach and attach the database.  These steps can be accomplished with either T-SQL commands or the SQL Server management studio (SSMS).  We will walk through an example of each technique in this tip

The following preliminary items should be reviewed prior to moving a transaction log file to a new location:

  • Record the current location, size, etc. of the database files
  • Record the current location, size, etc. of the transaction log file that is going to be moved
  • Note the location, size, etc. of the future destination of the transaction log file
  • Schedule a downtime to move the database when no users are connected to the application
  • Validate the database is not in any replication scheme, on a snapshot schedule or a member of a mirror
    • If so, plan accordingly and build the scripts to address these configurations
  • Ensure your are a member of the db_owner fixed role

In a nutshell, the three main steps involved in moving a log file to new location are:

  1. Detach the database
  2. Move log file to new location
  3. Attach the database by referencing the new location of the transaction log file

For demonstration purposes we will use the AdventureWorks database which is currently installed on the D:\ drive of my SQL Server. This database may be installed on another drive on your machine, but the main concept of the demonstration is to move the AdventureWorks transaction log file to another physical drive i.e. C:\.

Example - T-SQL Commands

In this example, we will work through the steps to move a transaction log file to a new location via T-SQL commands.  The first script will return the current locations, size, etc. of the database and transaction log files.

Script # 1: Capture database and transaction log file information

USE AdventureWorks
GO

sp_helpfile
GO

Below is sample output from the script showing that database has only two files.  First is primary database file and second is the transaction log file. Although the file name, size, etc. may be different on your SQL Server, you will know the exact location of the files before the transaction log move begins.  Note the location of the database file since it will be used during the database attach process.

Once you have the location information and have negotiated downtime with your users, now it is time to get exclusive access of the database in order to detach the database. If users are still connected to the database during the downtime, it is possible to remove them by using the With Rollback Immediate option or you can kill the connections via this tip.

Script # 2: Set database to single user mode and detach database

Use MASTER
GO

-- Set database to single user mode
ALTER DATABASE adventureWorks
SET SINGLE_USER
GO

-- Detach the database
sp_detach_db 'AdventureWorks'
GO

Now the database is detached.  Once the detach process is completed, then you can copy and paste the new transaction log file then delete the old transaction log file via Windows Explorer.  Once this is completed, we can attach the database with SQL Server database log file at new location with the following script:

Script # 3: Attach database with log file at new location

USE master
GO

-- Now Attach the database
sp_attach_DB 'AdventureWorks',
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\AdventureWorks_Data.mdf',
'E:\Move LogFile here through T-SQL\AdventureWorks_Log.ldf'
GO

After the final attach command our transaction log file has been moved to new location and our database is operational with log file on new location.  Verifying the new database transaction log location can be accomplished by re-running script #1 above.

 

Example - SQL Server Management Studio

In this example, we will complete the detach, move and attach process through SQL Server Management Studio (SSMS).  Here is the general process

  • Open SQL Server Management Studio
  • Navigate to root | Databases | AdventureWorks database
  • Right click on the AdventureWorks database
  • Select the Tasks | Detach... option

The Detach Database form will load with the applicable information.  Once the status is "Ready" then you can proceed with the detach process.  If status the status is "Not Ready" then you will get a reason under the message column.  Most of the time why the database is not ready is related to users connected to database.  If this is the case, you can select the drop option provided to drop all existing users.

Once the detach process is completed, then you can copy and paste the new transaction log file then delete the old transaction log file via Windows Explorer.  Once the move process is finished then the following general steps will attach the database via SQL Server Management Studio:

  • Open SQL Server Management Studio
  • Navigate to root | Databases
  • Right click on the Databases folder
  • Select the Attach... option
  • Click the Add button to navigate to the database and transaction log files

You may notice an error message that the transaction log file was not found after the database file was selected, so browse to the directory with the transaction log file and click the OK button.  At this point the attach should be completed and the database should be online with the new transaction log file location. To verify the database functionality and new file location, run script # 1 from above.

Next Steps

  • Click here to read more about attach and detach process of SQL Server databases
  • Click here to read more about setting the database to single user mode through SSMS
  • Click here to read more about setting the database to single user mode by using Alter database command through T-SQL
  • Click here to read more about primary data file and file groups architecture


Last Update: 6/17/2009


About the author
MSSQLTips author Atif Shehzad
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, October 30, 2012 - 10:46:24 AM - Atif Shehzad Read The Tip

@Marengga, Copy paste and delete is same as cut paste. However it is a bit more safe when we face any failure during the process. Failure like media, network, power etc.


Monday, October 29, 2012 - 3:04:48 AM - Marengga Read The Tip

Hi, may I know why do I have to copy from location --> paste to new location --> delete log file from old location

Is it different with cut log file from old location --> paste to new location ?


Thursday, September 20, 2012 - 11:47:25 PM - Atif Shehzad Read The Tip

@homer. Thanks for sharing your valueable exprerience.


Thursday, September 20, 2012 - 12:28:47 PM - homer Read The Tip

Learn from my mistake..before detaching.. make sure your account isn't using the same db as your default login database.  I've locked myself out and have to get another admin change my default login db.  


Thursday, July 02, 2009 - 6:44:49 PM - admin Read The Tip

Here is another tip that uses the method pointed out above:

How to move a SQL Server database within the same instance without losing settings
http://www.mssqltips.com/tip.asp?tip=1688


Thursday, July 02, 2009 - 12:05:37 PM - jhserres Read The Tip

While it is possible to still move a database (Data files or log Files independently) to a different file system location using detach/attach, there are potential unplanned consequences to doing so.


If your database uses Service Broker, by using detach/attach, Service Broker is disabled on the database, whereas when using ALTER DATABASE MODIFY FILE, Service Broker remains enabled.

Another problem that arises when using the detach/attach is having to deal with permission issues. The principals have to be given the corresponding rights over the securables, otherwise the attach will not be smooth.

BTW: detach/attach will be deprecated in future versions of MS SQL Server




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.