Move SQL Server transaction log to another disk

June 20, 2011 · 2 min read

When restoring a database backup, by default, SQL Server places the database files in the master database file directory.  In this example, that location is in L:\MSSQL10.CHTL\MSSQL\DATA as shown by the issuance of sp_helpfile

Default_directory_location_of_master_database

Hence, the restored files for the database CHTL_L2_DB are in the same directory

Default_location_of_restored__SQL_Server_files_

Per SQL Server best practices, the log file should be on its own disk drive so that the database and log file can operate in a sequential manner and perform optimally.

The steps to move the log file is as follows:

  1. Record the location of the database files and the transaction log files
  2. Note the future destination of the transaction log file
  3. Get exclusive access to the database
  4. Detach from the database
  5. Move the log file to the new location
  6. Attach to the database
  7. Verify new location of transaction log

Record the location of the database file

To view the current location of the database files, use the system stored procedure, sp_helpfile

1: use chtl_l2_db

2: go

3:  

4: sp_helpfile

5: go

sp_helpfile_output

Note the future destination of the transaction log file

The future destination of the transaction log file will be located in K:\MSSQLLog

Future_location_of_log_file

Get exclusive access to the database

To get exclusive access to the database, alter the database access to single_user.  If users are still connected to the database, remove them by using with rollback immediate option.  Note:  If you had a pane connected to the database when the it is placed into single_user mode, then you will be presented with a reconnection dialog box.

1: alter database chtl_l2_db

2: set single_user with rollback immediate

3: go

Detach from the database

Now detach from the database so that we can use windows explorer to move the transaction log file

1: use master

2: go

3:  

4: sp_detach_db 'chtl_l2_db'

5: go

After copying the transaction log file

After_copying_the_trans_log_file

re-attach to the database

1: use master

2: go

3:  

4: sp_attach_db 'chtl_l2_db',

5: 'L:\MSSQL10.CHTL\MSSQL\DATA\CHTL_L2_DB.MDF',

6: 'K:\MSSQLLog\CHTL_L2_DB_4.LDF',

7: 'L:\MSSQL10.CHTL\MSSQL\DATA\CHTL_L2_DB_1.NDF',

8: 'L:\MSSQL10.CHTL\MSSQL\DATA\CHTL_L2_DB_2.NDF',

9: 'L:\MSSQL10.CHTL\MSSQL\DATA\CHTL_L2_DB_3.NDF'

10: GO

This article is part of the GWB Archives. Original Author: Jim Lahman

New on Geeks with Blogs

  • We Won The One Award I Actually Care About

    Full Scale made the Inc. 5000 for the fifth year straight, the 12th listing across my three companies. Here is why the one award you cannot buy is worth stopping for.

  • Your Customers Build the Features Now

    I let a tool I liked sit dead for a year rather than build the features I wanted. An MCP server meant I never had to, and your customers can do the same to your product.

  • Get the Size of a Directory in Linux the Easy Way

    du -sh for the quick answer, ncdu for the cleanup, df for the disk itself: every command for checking directory size in Linux, plus why du and df never agree.

  • Vim Search and Replace: The Ultimate Guide

    One :%s command replaces every match in a file before a find dialog would even open. The Vim substitute patterns worth the muscle memory: flags, ranges, capture groups, and multi-file edits.