Remove transaction log files

Say you happened to get too many transaction log (ldf) files. Can you remove log files from the database? Yes, but only if a file isn’t in use, and you cannot remove the first (“primary”) log file.

So, be prepared to investigate the virtual file layout, using DBCC LOGINFO, to see if a log file is in use or not. You can find information about how to investigate the virtual log file layout in my shrink article. The basic steps are a bit similar to shrinking a log file: Investigate virtual log file layout, backup log, possibly shrink file, try removing it. Do this again as many times as it takes (repeat, rinse and lather).

Below is a script that, if you take the time to study it and play with it, will prepare you to remove transaction log files from a database. As always, don’t execute it if you don’t understand what it does!

USE master
IF DB_ID('rDb'IS NOT NULL DROP DATABASE rDb
GO

CREATE DATABASE rDb
ON
PRIMARY
NAME N'rDb'FILENAME N'C:\rDb.mdf' SIZE 50MB FILEGROWTH 1024KB )
LOG ON
(NAME N'rDb_log2'FILENAME N'C:\rDb_log2.ldf'SIZE 3MBFILEGROWTH 2MB)
,(NAME N'rDb_log3'FILENAME N'C:\rDb_log3.ldf'SIZE 3MBFILEGROWTH 2MB)
,(NAME N'rDb_log4'FILENAME N'C:\rDb_log4.ldf'SIZE 3MBFILEGROWTH 2MB)
GO

ALTER DATABASE rDb SET RECOVERY FULL
BACKUP DATABASE rDb TO DISK = 'C:\rDb.bak' WITH INIT
CREATE TABLE rDb..t(c1 INT IDENTITYc2 CHAR(100))

INSERT INTO rDb..t
SELECT TOP(15000'hello'
FROM syscolumns AS a
CROSS JOIN syscolumns AS b

--Log is now about 46% full
DBCC SQLPERF(logspace)

--Check virtual log file layout
DBCC LOGINFO(rDb)
--See that file 4 isn't used at all (Status = 0 for all 4's rows)

--We can remove file 4, it isn't used
ALTER DATABASE rDb REMOVE FILE rDb_log4

--Check virtual log file layout
DBCC LOGINFO(rDb)

--Can't remove 3 since it is in use
ALTER DATABASE rDb REMOVE FILE rDb_log3

--What if we backup log?
BACKUP LOG rDb TO DISK = 'C:\rDb.bak'

--Check virtual log file layout
DBCC LOGINFO(rDb)
--3 is still in use (status = 2)

--Can't remove 3 since it is in use
ALTER DATABASE rDb REMOVE FILE rDb_log3

--Shrink 3
USE rDb
DBCC SHRINKFILE(rDb_log3)
USE master

--... and backup log?
BACKUP LOG rDb TO DISK = 'C:\rDb.bak'

--Check virtual log file layout
DBCC LOGINFO(rDb)
--3 is no longer in use

--Can now remove 3 since it is not in use
ALTER DATABASE rDb REMOVE FILE rDb_log3

--Check explorer, we're down to 1 log file

--See what sys.database_files say?
SELECT FROM rDb.sys.database_files
--Seems physical file is gone, but SQL Server consider the file offline

--Backup log does it:
BACKUP LOG rDb TO DISK = 'C:\rDb.bak'
SELECT FROM rDb.sys.database_files

--Can never remove the first ("primary") log file
ALTER DATABASE rDb REMOVE FILE rDb_log2
--Note error message from above

Leave a Reply

Your email address will not be published.