Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Remove Unnecessary SQL Server Transaction Log Files


By:   |   Read Comments (7)   |   Related Tips: More > Database Administration

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

SQL Server allows to use more than one transaction log file, but the question arises whether it is necessary and what is the benefit of having multiple transaction log files. There is a misconception among some developers that having multiple transaction log files can increase performance because SQL Server can use them in parallel. SQL Server uses only one transaction log file at the moment and there is no parallelism in this case. Sometimes more than one log file can be needed for the purpose of troubleshooting. So, normally there is no necessity to have more than one log file. Let’s consider a case, when our database has more than one log file and we should retain only one. This tip aims to describe how to remove unnecessary log files correctly and retain only one.

Solution

Before starting to illustrate how to remove unnecessary log files, let's briefly describe how SQL Server works with log files: when database has more than one log file, SQL Server keeps writing to the first one until it's full, then switches to the second and so on. After the last log file becomes full SQL Server returns back to the first one and the cycle continues. However, as we mentioned, sometimes more than one log file may be required. For example when the disk, where the log file is located becomes full and we need to create the second log file in another location, but after troubleshooting the problem, we should delete the second log file, because there is no use to have more than one log file. Now, let's assume that we have a database with two log files, and our task is to remove the second one. The following script creates the TestDB database with two log files and TestTable table. To run it by yourself, you will need to replace “D:\SQL Data” with an existing folder path.

USE [master]
GO

CREATE DATABASE [TestDB]
 ON  PRIMARY 
( NAME = N'TestDB', FILENAME = N'D:\SQL DATA\TestDB.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestDB_log1', FILENAME = N'D:\SQL DATA\TestDB_log1.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%), 
( NAME = N'TestDB_log2', FILENAME = N'D:\SQL DATA\TestDB_log2.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

USE TestDB
GO

CREATE TABLE TestTable
(
	ID INT IDENTITY(1,1),
	Value BIGINT
)

--Change recovery model for TestDB to FULL (if your model database is in FULL recovery model, TestDB has been already created in this mode)
ALTER DATABASE TestDB SET RECOVERY FULL 

Now let's understand the transaction log physical structure: Internally transaction log file consist of Virtual Log Files (VLF), which are the unit of management in the transaction log file. It means that when database engine grows or shrinks the log file, it does that with complete VLFs (for example it can't shrink half of the VLF). The size of virtual log files as well as their number in the physical log file isn't fixed and is managed dynamically by the database engine. To monitor the transaction log file internally we use "DBCC LOGINFO" command, which provides information about virtual log files. In the script below we used this command for our database:

--Getting log files info
DBCC LOGINFO('TestDB')

And the result is the following:

TestDB

FileID is log file IDs for our database. Status indicates is VLF reusable or not (possible values: 0 - yes, 2 -no). As we can see there is only one VLF with Status=2. Now when we will insert data into the TestTable and monitor how log files are growing:

USE TestDB
GO

--Checking log information before insertion
SELECT file_id, name, type_desc, physical_name, size, max_size
FROM sys.database_files


--Inserting data into TestTable
;WITH ValueTable AS
(
	SELECT 1 n
	UNION ALL 
	SELECT n+ 1
	FROM ValueTable
	WHERE n < 10000 --Value 10000 is used to facilitate testing process, please be careful in choosing this value for your server to avoid overloading it
) 
INSERT INTO TestTable (Value)
SELECT n
FROM ValueTable 
OPTION (MAXRECURSION 0)


--Checking log information after insertion
SELECT file_id, name, type_desc, physical_name, size, max_size
FROM sys.database_files

--Getting log files info
DBCC LOGINFO('TestDB')

With this example we can see that both log files grew and now there are VLFs with "Status=2" in the second log file (FileID=3) also:

VLFs_with_Status=2

Now we need to remove TestDB_log2.ldf file. Note, that we can remove only the secondary log files. Removing the primary log file is not allowed by SQL Server. Each database has only one primary log file and the first log file which is created in the database creation script is considered the primary. If we try to remove the second log file:

USE master
GO

--Remove TestDB_log2 file
ALTER DATABASE TestDB REMOVE FILE TestDB_log2

We will receive the following message:

ALTER_DATABASE_TestDB

We can remove the transaction log file only when it's empty, therefore we first need to empty it. To do that, we should back up the transaction log. Since our "TestDB" database is newly created and there are no full backups, we need to issue a full database backup for the TestDB database, after which we can issue a transaction log backup:

--Full backup
BACKUP DATABASE TestDB TO DISK =N'D:\SQL DATA\TestDB.bak'

--Transaction log backup
BACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'

The transaction log backup truncates the log file (there are some exceptions, which are out of scope of this tip). Log truncation deletes inactive virtual log files from the start of the logical log and releases space in the log file. However, truncation does not reduce the size of a physical log file. It only frees space in it, which can be reused. Let’s run "DBCC LOGINFO" again:

DBCC LOGINFO

As we can see there are no virtual log files in the "TestDB_log2.ldf" file with Status=2 and now our log file is empty and ready for removal:

--Remove TestDB_log2 file
ALTER DATABASE TestDB REMOVE FILE TestDB_log2

The removal is successful.

Remove TestDB

However when we check log the information again, we will see that the logical log file still exists:

--Checking log information
SELECT file_id, name, type_desc, physical_name, size, max_size
FROM sys.database_files	

sys.database_files

If we do another log backup, the file will be deleted:

--Transaction log backup
BACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'

--Checking log information
SELECT file_id, name, type_desc, physical_name, size, max_size
FROM sys.database_files	

Checking log information
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 6 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, October 11, 2017 - 9:21:40 AM - dsil Back To Top

 Very nice article, helped me delete the unwanted log files on an archive database.   This solution should be on stackoverflow and microsoft so more people can find it.   There's lots of non-working solutions for this problem out there.


Thursday, December 03, 2015 - 1:05:34 PM - Sergey Gigoyan Back To Top

Eric, 

Taking a Full backup does not cause transaction log file truncation. In Full or Bulk-Logged recovery models, transaction log file is truncated after log backup(but there are some exceptions), in Simple recovery model - after checkpoint (note, when database backup is taken, checkpoint occurs). In simple recovery model we can remove log file without taking a Full backup (however even under Simple recovery model it is possible that we will receive "The file <log_file_name> cannot be removed because it is not empty." message). Moreover, when database is in Full recovery model, and no Full backups have been taken yet, you can remove secondary log file like in case of Simple recovery model. But when database is in Full recovery model and at least one Full backup have been taken and log file contains log records, you cannot remove it without taking transaction log backup (without truncating the log file).


Wednesday, December 02, 2015 - 11:49:45 AM - eric81 Back To Top

Sergey,

I wanted to update my initial question from Tuesday 12/01/2015.  When your database is in SIMPLE recovery mode you wouldn't be able to perform a LOG BACKUP so my thought is you should simply need to perform a FULL backup of your database then exam the VLF information through DBCC LOGINFO command.  If the secondary log file shows a status of 2 you can't remove it, but I suspect that shouldn't happen when you are in SIMPLE recovery model.


Wednesday, December 02, 2015 - 7:09:42 AM - Sergey Gigoyan Back To Top

Eric, in Simple recovery model, log file is truncated after checkpoint. There is no reason to change recovery model to Full.


Wednesday, December 02, 2015 - 7:08:38 AM - Sergey Gigoyan Back To Top

Raghavender, unlike data files, it is not possible to move log records from one log file to other, so executing DBCC SHRINKFILE (, EMPTYFILE) command does not give a guarantee that log file becomes empty. 


Tuesday, December 01, 2015 - 12:12:57 PM - eric81 Back To Top

What if your database is in SIMPLE Recovery Mode would you revert the database to FULL recovery or simply perform a DBCC SHRINKFILE command?


Tuesday, December 01, 2015 - 4:39:10 AM - Raghavender Chavva Back To Top

We can use DBCC SHRINKFILE (filename, EMPTYFILEalso, right ? for emptying the file.


Learn more about SQL Server tools