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

By:   |   Comments (16)   |   Related: 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 Command

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.

adventure works

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
object explorer

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.

detach database

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
local database 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, March 18, 2024 - 12:35:20 PM - Greg Robidoux Back To Top (92087)
Thanks the script has been updated.

Monday, March 18, 2024 - 11:44:37 AM - Chris Fisher Back To Top (92086)
In your first script... you are missing the letter "U" for use:
(shown below):
--Script #1: Capture database and transaction log file information

SE AdventureWorks
GO

sp_helpfile
GO

Tuesday, May 26, 2020 - 4:06:36 AM - Rob Back To Top (85764)

Hi & thanks for the tips.

I am trying to move my log file to a network location on another server. I have detached the db, and moved the log file to the new location. When I try to re-attach, I do not get the option to connect to any other drive except the local C drive. I have a mapping on th db server pointing to the other server. As far as I can tell, there is no way to enter a network location.

Am I missing something? Is what I am trying to do possible? I was wanting to save the logs to our file server which is then backed up daily.

Any advice would be greatly appreciated.

Rob

Wednesday, November 13, 2019 - 5:30:50 PM - Daisy Back To Top (83084)

Thank you so mcuh for this post

for me I combine both together to move MDF and LDF to new location, the key is you have to use single-user by T-SQL, and detach and attach database by SSMS

the last step, confirm MDF and LDF are in new location

Wednesday, December 6, 2017 - 12:16:44 PM - Mike Back To Top (73681)

Wonderng if anyone else has experienced what I'm seeing in SQL 2014 and 2016 SSMS when you attempt to attach a database.mdf file from another instance where the wizard doesn't bring in the log?  When I attached a database to any 2008r2 instance or lower in SSMS, the log file shows up and I browse and change location and all is good.  It's like the meta info for the log file is not being read from the .mdf.  Work around of course is attaching databases via t-sql.  Just wondering if these is a bug or something isolated to my enivironment.

thanks

 

Tuesday, September 19, 2017 - 8:04:58 AM - SQLSugs Back To Top (66414)

 If you're going to set database to single user mode you need to make sure AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. Otherwise, as BOL states, "When this option is set to ON, the background thread that is used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode."

 

Tuesday, March 21, 2017 - 5:12:17 PM - Logan Back To Top (51516)

I spent way too much time trying to find how to move database files. Why does Microsoft make it so dang hard to move database files. Anyways, thanks for the help! Bookmarking this page. 

Saturday, July 30, 2016 - 12:12:03 PM - Jon Cohn Back To Top (43014)

Hello,

This method should be deprecated in favor of using Alterdatabase 

 

Wednesday, October 7, 2015 - 10:02:27 AM - Adam Back To Top (38835)

Hello Atif,

Thanks for your writeup. It worked like a charm using the SQL SMS! I just had to adjust the security on the new location where I saved my log files.

Cheers,

Adam

Monday, August 10, 2015 - 10:11:34 PM - astroboy22 Back To Top (38416)

Mabuhay! Hi Sir Atif thanks for this guide :). We just seperate our log file now and wee what will be the effect. Our HQ Client Database is now 99GB and store connection now up to 70 stores. Thats one hard thing we are facing now. Its a bottle neck on VPN part and HQ uses here in our office. Hope this seperation makes things smooth :).

By the way our supplier quote this one for us, any comments and suggestions?

x3650 M5, Xeon 8C E5 2630v3 85W 2.4GHz/1866MHz/20MB, 1x16GB, O/Bay HS 2.5in SAS/SATA, SR M5210, Multiburner+ODD Cable, 550W p/s, Rack 1
Intel Xeon Processor E5-2630 v3 8C 2.4GHz 20MB Cache 1866MHz 85W 1
16GB TruDDR4 Memory (2Rx4, 1.2V) PC4-17000 CL15 2133MHz LP RDIMM 7
1TB 7.2K 6Gbps NL SAS 2.5in G3HS HDD 3
System x3650 M5 PCIe Riser (2 x8 FH/FL + 1 x8 FH/HL Slots) 1
System x 550W High Efficiency Platinum AC Power Supply 1
Windows Server 2012 R2 Standard ROK (2CPU/2VMs) - MultiLang 1
Windows Server CAL 2012 (5 Device) - Multilanguage 1
System x3650 M5 Lockable Bezel 1
System x Enterprise 2U Cable Management Arm (CMA) 1
COM Port Bracket 1
Lightpath LCD Op Panel 1
Integrated Management Module Advanced Upgrade 1
4.3m, 10A/100-250V, C13 to IEC 320-C14 Rack Power Cable 2



Bbig thanks, 
Christopher Baradi 

Tuesday, October 30, 2012 - 10:46:24 AM - Atif Shehzad Back To Top (20148)

@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 Back To Top (20117)

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 Back To Top (19605)

@homer. Thanks for sharing your valueable exprerience.

Thursday, September 20, 2012 - 12:28:47 PM - homer Back To Top (19594)

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 2, 2009 - 6:44:49 PM - admin Back To Top (3686)

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 2, 2009 - 12:05:37 PM - jhserres Back To Top (3682)

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














get free sql tips
agree to terms