Moving Transaction Log file from one location to another can be done in a few different ways. One of the most popular method is detaching and attaching a database. In this article I will try to show this method. Before I do that, let's look at a few possible reasons of why Transaction Log file may need to be moved:

1. Currently used disk is running out of space. Moving the file to a bigger disk is a solution.
2. In a multi-database instance, moving some of the log files to a different disk may increase performance.
3. Currently used disk is old and should be replaced with a new one: faster, more reliable.
4. A log file was created at a wrong location. After discovering that, the file should be moved to a proper directory.

Current state

I have TestDb database. Sys.database_files system view contains a list of database files. It is a per-database view so it contains information about a database on which it is queried.

select file_id, type, type_desc, name, physical_name, state, state_desc, size
from sys.database_files

My database has one transaction log file located on E drive.

Log file size can be checked using dbcc sqlperf.

declare @logSpace table (
    dbName varchar(100),
    logSizeMB float,
    logSpaceUsed float,
    status int
)

insert into @logSpace
execute('dbcc sqlperf(''LogSpace'')')

select *
from @logSpace
where dbName = 'TestDb'

It shows about 21% of 300 MB file is used. I want to move it to F drive.

Reduce file size

The file size is not big in this case but in most cases it might be better to shrink the file before moving it. Unfortunately, moving a log file using detaching and attaching a database requires an outage - the database will be unavailable for some time. Based on this, reducing the file size may make the whole process shorter which is definitely better.

At the beginning, I execute checkpoint command to apply all data changes to datafiles. It makes all transaction log entries not needed for database recovery. As they will not get automatically deleted from the file, transaction log backup will need to take care of it.

checkpoint
backup log [TestDb] to disk = N'G:\msdb\TestDb.trn' with noformat, noinit, name = N'TestDb-Transaction Log Backup', skip, norewind, nounload, stats = 10

11 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
71 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 7779 pages for database 'TestDb', file 'Log' on file 1.
BACKUP LOG successfully processed 7779 pages in 1.661 seconds (36.587 MB/sec).

Lets check the log file usage again:

declare @logSpace table (
    dbName varchar(100),
    logSizeMB float,
    logSpaceUsed float,
    status int
)

insert into @logSpace
execute('dbcc sqlperf(''LogSpace'')')

select *
from @logSpace
where dbName = 'TestDb'

Now, lets run checkpoint command again and shrink the log file.

checkpoint
go
use [TestDb]
go
dbcc shrinkfile (N'Log' , 0, truncateonly)
go

Move the log file

Now, when the transaction log file is small, the database outage can start. I shutdown all applications using the database and kill all remaining connections.

Once it is done, the main part can begin - moving the file. There are three steps.

First one is detaching the database. It can be done using SSMS or sp_detach_db function.

use [master]
go
exec master.dbo.sp_detach_db @dbname = N'TestDb'
go

It makes the database unavailable without deleting database files. Then, the real database outage starts.

Second step is moving the log file to a new location on the operating system level. Just to be on the safe side, it is good to make a backup of the files, just in case.

Third step is attaching the database. There is sp_attach_db function but as it is marked as deprecated in SQL Server 2012. I use a recommended method - create database for attach.

use [master]
go
create database [TestDb] on 
( filename = N'F:\msdb\TestDb.MDF' ),
( filename = N'F:\msdb\TestDb.LDF' ),
( filename = N'F:\msdb\TestDb_TestFileGroup.ndf' )
 for attach

Finalize

After attaching the database, it should be operational, but there are still some actions that should be performed like mapping users to logins or executing dbcc checkdb.

Besides that, I want to extend the log file to its initial size - if you remember, I shrank it before moving. Extending can be done by alter database modify file command.

use [master]
go
alter database [TestDb] modify file ( name = N'Log', size = 307200KB )
go

And a very important action - always remember to take a full database backup after such operation.

backup database [TestDb] to disk = N'G:\msdb\TestDb.bak' with noformat, noinit, name = N'TestDb-Full Backup', skip, norewind, nounload, stats = 10

At the end, I check the database files location:

select file_id, type, type_desc, name, physical_name, state, state_desc, size
from sys.database_files

The log file is stored on F drive as needed.