Move SQL Server transaction log files to a different location via T-SQL and SSMS Bottom of Form
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?
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
- Detach the database
- Move log file to new location
- Attach the database by referencing the new location of the transaction log file
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|
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|
-- Set database to single user mode
ALTER DATABASE adventureWorks
-- Detach the database
|Script # 3: Attach database with log file at new location|
-- Now Attach the database
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\AdventureWorks_Data.mdf',
'E:\Move LogFile here through T-SQL\AdventureWorks_Log.ldf'
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.