Solution for full transaction log

In this article, I discuss the solution for the error mentioning that the transaction log for your SQL database is full due to 'ACTIVE_TRANSACTION'. This error means that you SQL Server database has the wrong settings for the task that you want to perform.

Most likely, you are trying to bulk import a large data set from a plain text file or Excel file. Or your program or database executes an infrequent task or processes very large transactions, causing the database to throw these errors. In this article, we will explain how you can prevent the transaction log error in SQL Server while bulk importing your data.

Quick fix the full transaction log error

First of all, to fix the error due to full transaction log is very simple. The ACTIVE_TRANSACTION can give a good hint: the current transaction is filling up the transaction log for the database. And when the transaction log cannot grow anymore, you will get the full transaction log error.

You can solve this issue easily within your database. First of all, you can run a script to solve this. However, in this article we show you how to solve this by changing the properties of the database.

So, to solve this SQL Server error, the transaction log needs to be able to grow more than what is currently possible. You have to change this setting in the SQL Server database itself.

When you have logged in to the SQL Server instance using SQL Server Management Studio, you will see a list of databases that are on this SQL Server instance. From this list of databases, identify the database where SQL DataTool is connected to (or at least the database that returned the error). Now you select the properties of that database, by opening the context menu (right click on the database name). The context menu is the pop-up as shown in the screenshot below.

Within the properties screen for the database, multiple 'pages' are available (as shown on the left hand side of the screenshot below). The settings that you have to change, can be found on the 'Files' page. This page shows (at least) two files: one file that contains all the database data, and one file that contains the log of all transactions executed in the database. This second file has the file type LOG. This file contains the transaction log, and causes the transaction log full due to ACTIVE_TRANSACTION error.

The two files mentioned are the minimum number of files. Some databases contain more files, but as mentioned, in this case you should focus on the LOG file. Both files are created during the creation of the database. During the creation of the database, all settings are configured. Most likely, bulk insert is not one of the scenarios that was taken into account when creating the database.

As you can see in the screenshot above, in the file settings of the log file of this database the 'Autogrowth / Maxsize' property is set to 'None'. This means that the transaction log can not grow at all. Even when you will try to bulk insert a small data, you might already get the transaction log is full due to ACTIVE_TRANSACTION error.

To change the settings of the 'Autogrowth / Maxsize' property, click on the Change button (button with the ellipsis) behind 'None' (like in the screenshot above). On the screen that pops up, you can change the Autogrowth settings. A screenshot of this screen is shown below.

This screen shows the following settings:

• Enable autogrowth. Checking this option will enable the autogrowth of the file chosen. In this case, when we enable autogrowth, the transaction log file will be enabled for autogrowth.
• File growth. File growth has two options. Each time an autogrow event is triggered, the transaction log file will be extended. You can either enable the transaction log file to grow with a certain percentage, or you can set the autogrowth to be always the same size (in Megabytes).
• Maximum file size. The maximum file size has also two options. The file size can be either limited to a certain size (in MB) or the file can be set to grow unlimited (where the space of the disk used is the maximum).

By checking the 'Enable Autogrowth' option in this screen, the other settings become available. You can either limit the transaction log file to a specific maximum file size, or let the log file grow to an unlimited size. Now you know where you can find the solution of the error that the transaction log of your database is full due to ACTIVE_TRANSACTION, you can solve it!

All programs communicating with the database are dependent on the SQL Server database limitations. Therefore you might want to know what the transaction log does. In the next section, the transaction log is explained shortly, and some useful links are added.

What is the transaction log

The transaction log file is a file which is part of the database. The data (either ROWS data or COLUMN data) is stored within a file. The (transaction) log file is stored on the disk in a different file.

The transaction log file contains specific transactions with the purpose of restoring the database to its state before the transactions had taken place. This means that, when a transaction result in an error, the transaction can be rolled back. To find more details on the transaction log, you can look at this beginner's guide.

When you enable the transaction log for autogrowth, it is important to keep in mind that an autogrow event will temporary stop transactions to be executed. So an autogrow event on a transactional production system will temporary stop the (business) transactions.

When the file growth setting has been set to grow in percentage, the size of the growth depends on the transaction log file. When the file is small, the file will only grow with a limited amount. When the file is already huge, the growth of the file will also be huge. Therefore, to be able to keep track of the size of the transaction log, it is better to set the 'File growth' setting to grow in megabytes.

And when the maximum file size is set to unlimited, you might run into the problem that the disk is full. When you limit the file size by a specific maximum, you might run into the transaction log is full due to ACTIVE_TRANSACTION error.

When bulk importing data, you should take the maximum file size into account. To limit the risk of autogrow events from happening (and thus stopping business transactions on a production system), you can choose to set the transaction log to the size of your largest table.

You can find on the pages of Microsoft other considerations and best practices on the autogrowth option.

Finishing bulk inserts

When you have finished the bulk inserts, the settings of the transaction log can be set to the initial configuration. Only when the initial settings were thoroughly thought through, and when you are not expecting to bulk insert any other data. Keeping the transaction log at a large file size will use a lot of disk space. And this cannot be used for other data anymore.