How to Solve Error When Transaction Log Gets Full? – Interview Question of the Week #272

Question: How to Solve Error When Transaction Log Gets Full?

Answer: This was a question I was asked in the recent Comprehensive Database Performance Health Check, by my client. The answer is very simple but we will understand that with the help of a real-world demonstration.

To simulate the scenario, first I created a TestDB and restricted the file growth.

CREATE DATABASE [TestDB]
 ON  PRIMARY 
( NAME = N'TestDB', FILENAME = N'D:\TestDB.mdf' , 
	SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'TestDB_log', FILENAME = N'D:\TestDB.ldf' , 
	SIZE = 8192KB , MAXSIZE = 8192KB , FILEGROWTH = 8192KB )
GO

You can see that in the above script I have mentioned that the max size of the Log file will not be more than 8 MB.

Now let us create and populate dummy data in this database and see how the database will react when it fills up the log file.

CREATE TABLE FirstIndex (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO

Next, we will populate this table with data that is larger than 8 MB.

[SQL]INSERT INTO FirstIndex (ID,FirstName,LastName,City)
SELECT TOP 1000000 1,
‘Bob’,
‘Brown’,
‘Houston’
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO[/sql]

When you run the above statement, it will give you the following error:

Msg 9002, Level 17, State 4, Line 1
The transaction log for database ‘TestDB’ is full due to ‘ACTIVE_TRANSACTION’.

This is because our Log file is restricted to only 8 MB and the data which we are inserting is larger than 8 MB. The reason, I have limited the log file to the 8 MB as I wanted to simulate a similar situation when we have left the log file to grow unlimited and the disk space is limited. When you run out of disk, you may usually get this error.

Workaround/ Solution: T-Log Gets Full

The workaround of this is pretty simple.

Option 1: You can increase your log file if possible on the disk by removing the restriction to grow.

Option 2: If your log file is filled up, it is a good idea to take log backup and clean up your log file. However, this will not work if your transaction is still bigger than available space on your log file.

Option 3: You may try out to change the recovery model to simple and try your transaction again, however, the success rate of this solution is very low and also there is a chance that you are risking your entire backup strategy. Do not do this until you run out of all the options.

Option 4: Add another log file on the different disk and run your transactions again. Once the transaction is completed, you can clean up your transaction log by following option 1 or option 2. You can follow the direction to add a new file over here: SQL SERVER – Multiple Log Files to Your Databases – Not Needed.

USE [master]
GO
ALTER DATABASE [TestDb] ADD LOG FILE ( NAME = N'NewLog', FILENAME = N'C:\NewLog.ldf')
GO

Once you add another log file and attempt to run the transaction again, you will see it goes through fine. While we are at it, I strongly suggest that you read the blog post here which describes why you should not add another log file in the normal circumstances.

Reference: Pinal Dave (http://blog.SQLAuthority.com)