SQL Server Error: Fixing The Transaction Log for Database is Full Due to log_backup

If you are working with SQL Server, you may see an ominous sounding error: the transaction log for database {Your Database Name} is full due to log_backup

This error can be due to one of two things:

1) The physical drive on which your database files reside is running out of space
2) Your database’s transaction log was set with a maximum file size that has been reached

Before doing anything else, you should check that the physical drive still has room. If it doesn’t, you need to increase the size, or to move the log file to a drive that has more space.

If your physical drive has lots of room, you’ll have to check the settings for your database’s transaction log file. It’s likely that a maximum file size was specified for the transaction log file, which is now the cause of the log errors. I’ve seen some solutions that you can use the DBCC ShrinkFile command to try to free up some space, but I have not found this to be a useful solution, and you will soon hit the same problem again.

Step #1: Increase the Maximum Transaction Logs File Size

You won’t be able to make any database changes until the transaction log file size setting is increased, so the first goal is to increase the size limit on your transaction log file. Our end goal is to set the Transaction log file growth to unlimited so this error won’t happen again, but before you can do so, you need to alter the maximum file size settings so that you can make and save database changes.

As part of increasing the maximum file size, you’ll need to know the physical name of the transaction log file on your system drive. You can find this out by running the following query in SSMS:

USE YourDatabaseName
GO
select * from sys.database_files

In the results, look for the ‘name’ for the row entry that has a type_desc of ‘LOG’

Now that you have the log file name, run the below script to alter the file size of the log:

ALTER DATABASE DatabaseName
MODIFY FILE
(NAME = DatabaseLogFileName,
SIZE = 2000MB);

Make sure size is bigger than the current size. By default the size limit may have been set to 1GB, so this statement doubles the size to 2GB. Obviously you’ll need to check if this is also the case for your transaction file size and update the statement to your needs.

Step #2: Set the Transaction Logs File size to unlimited growth

Now that you have increased the maximum size of your database transaction log file, you’ll once again be able to make changes. You can avoid seeing the transaction log file in future by making sure that the file is set to permit unlimited growth. Then only the size of your physical drive will be the limiting factor, and at the low price of hard drive space these days, that’s not a bad idea.

The easiest way to set the transaction log file to allow unlimited growth is by managing the database properties in SSMS:

1. First log into the management console using an administrative account.
2. Then right-click on the database name and click Properties
3. In the properties window click on the Files option in the left hand navigational bar
4. Then in the main window look for the file type that is LOG, and click the button inside the Autogrowth/Maxsize column
5. Click the checkbox to Enable Autogrowth
6. Click Ok to save and apply your change.