THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

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
Published Wednesday, June 17, 2009 8:47 AM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Linchi Shea said:

> As always, don't execute it if you don't understand what it does!

My mode of learning is first executing it to see what happenns and then studying it :-)

June 17, 2009 12:05 PM
 

EvilPostIT said:

Is it possible to change the primary log file to be another log file or is this internal?

July 6, 2009 1:15 PM
 

TiborKaraszi said:

I don't think you can change the primary log file. So, if you need to move it, you are probably in for a brief period of off-line for the database (procedure for moving database files is described in Books Online).

July 6, 2009 1:24 PM
 

ffx100 said:

It does not matter what I do, I cannot delete second log file. I am getting error saying "... file is not empty ...". Loginfo shows status 0 for second file, DBCC SHRINKFILE command returns current and minimum sizes = 153.

September 27, 2011 12:03 PM
 

puvy said:

can u explain what this scrip does?

March 15, 2013 1:54 PM
 

rpbos said:

Tibor, I just used your script above in our environment and it worked perfoectly.  Thanks so much for the clear detailed explanation.

May 21, 2013 6:36 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement