Rajendra Gupta
comparison on transaction log growth between Accelerated Database Recovery feature On and Off.

Accelerated Database Recovery and Long Running Transactions with Transaction Log Growth

March 21, 2019 by

In my previous article in this series Accelerated Database Recovery; Instant Rollback and Database Recovery, we talked about a potential DBA painkiller to resolve long waiting times for database recovery and rollback scenarios using Accelerated Database Recovery. In this article, we will look at one more painful challenge for DBAs, Long Running Transaction with Transaction log growth.

You might have faced a situation in which a long-running query is causing excessive log growth. Due to an active transaction, SQL Server is not able to truncate the logs even in simple recovery model. You have limited space on the drive where the log file is situated. Suppose you have very limited free space remaining, then the only option is to kill the process, but again it will require transaction log space. We cannot control the behaviour of the transaction log to avoid such excessive log growth during long running transactions. Wait, don’t give up yet … The solution is described in further sections of this article.

Before we move on, let us look at SQL Server transaction log behaviour in brief.

SQL Server database consists of the following files.

  • Primary data file (.mdf)
  • Secondary data file (.ndf) (optional)
  • Transaction log file (.ldf)

SQL Server Databases architecture

A transaction log file is a circular file that consists of Virtual Log Files (VLF). SQL Server logs the transaction in the transaction log, and when it reaches to end of the file, it reaches to the beginning of the log.

transaction log file architecture

SQL Server runs the process to mark the VLF inactive if the recovery process does not require them. In the simple recovery process, you can see the log truncation after the transaction is committed. In Full recovery model, it requires log backup to complete before log truncation. If we do not have sufficient free space in drive or transaction log does not have space to grow due to max size limitation, you get the error 9002 of severity 17.

You can query sys.databases to identify why log space cannot be used.

It can have the following values:

  • Replication
  • Database mirroring
  • Log scan
  • Always On Availability group

In the following image, you can see the basic overview of transaction log circular behaviour.

transaction log file architecture

Suppose we have a log running active transaction for a particular database, SQL Server cannot truncate the logs due to ACTIVE_TRANSACTION. You cannot truncate the logs in FULL or BULK-LOGGED and Simple recovery model.

In the following screenshot, you have a transaction log file with five VLF

transaction log file example

If the logs are truncated, you can see the transaction log structure as follows.

transaction log file example

Let us view the log running transaction issue with transaction log in the following example.

Environment Detail for this demo

  • In this demo, I am using SQL Server 2019 instance without enabled Accelerated Database Recovery feature. You can use any supported SQL Server version for this demo

In SQL Server 2019 CTP, you can use the following query to check the status of this feature. If you are using other than SQL Server 2019 you check to perform this initial demo however in a later section; you need to use SQL Server 2019 that you can download from this link.

Check is_accelerated_database_recovery_on feature on SQL database

  • We need to restrict the maximum transaction log size to 2 GB for this demo. Run the following Alter Database command to do it. We can do it using SSMS database properties page as well.

  • Put the database into the Simple Recovery Model. SQL Server should truncate the logs once a transaction is committed.

    Modify recovery model to SImple

Before running the transaction, let us view the transaction log size using DMV sys.dm_db_log_stats

In the following screenshot, you can see that currently we have only 0.14 MB active log size. We do not any running process to hold log truncation.

sys.dm_db_log_Stats output to view log status

We want to capture the log growth during an active transaction therefore, create a SQL table to

Insert output from the DMV query mentioned above using SQL agent job.

Configure a SQL Agent job to run following query every 30 seconds. In SQL Server Agent right -click on Jobs and create new job.

In the Job step, paste the query to insert a record into TLogGrowth table

create job to capture data using sys.dm_db_log_Stats output

Specify the job frequency to every 30 seconds.

Set SQL Server Agent job schedule

Now, run the following query to start the transaction. Please make sure SQL Server Agent should be running to capture transaction log growth.

In the output, you can see that the transaction log for database is full due to Active Transaction. It inserted only 2949 rows due to a limited maximum transaction log size.

trasaction failed due to trasaction log full issue

Let us check the record in the tblSQLShackDemo table for the log file growth. We can see here that the total log size is 20147 MB and active log size is 951 MB. SQL Server could not truncate the transaction log due to ACTIVE_TRANSACTION as shown in the log_tuncate_holdup_reason column of the following output.

view the trasaction log growth

SQL Server could not complete this particular transaction. Imagine this situation in a production environment where the transaction log is full due to an active transaction. You might have sufficient free space available. However, it would continue to grow the log file. It is not right in case of any disaster as well. SQL Server will have to rollback a complete transaction that might result in huge undo time. It is again a potential panic situation for DBA. We cannot control this behaviour of SQL Server. We can only ask developers to write down the efficient code and commit transaction frequently.

Let us run this transaction in SQL Server 2019 with enabled Accelerated Database Recovery feature.

In the following screenshot, you can see the Accelerated Database Recovery feature is enabled on SQLShackDemo_ADR.

enabled Accelerated Database Recovery feature.

We will execute a similar workload in this database as well. We need to create SQL table in SQLShackDemo_ADR database and point the SQL Server Agent job as well on this ADR enabled database.

Create job on Accelerated Database Recovery database to capture log growth

Execute the same workload on Accelerated Database Recovery database. We are successful this time. Query executed successfully and inserted 10,000 records into a tblSQLShackDemo table in approximately 13 minutes.

Query executed successfully in Create jAccelerated Database Recovery database

It is interesting to know transaction log usage for this database having Accelerated Database Recovery.

LOg growth status jAccelerated Database Recovery database

We have the following observation regarding the log size

  • Total_vlf_count remains constant after the initial increment
  • Log_truncation_hold_reason is OLDEST_PAGE. It shows that the oldest page in this database is older than the checkpoint LSN

In the following chart, we can see the comparison on transaction log growth between Accelerated Database Recovery feature On and Off

comparison on transaction log growth between Accelerated Database Recovery feature On and Off.

Initially, a transaction log grows in the Accelerated Database Recovery enabled database. It remains constant after initial growth. It is because we are using Persistent Version Store in and secondary log stream to store all necessary log records that we can go back and truncate the portion of the log because SQL Server does not require these for recovery and rollback. It is the reason SQL Server quickly rollback any transaction for Accelerated Database Recovery enabled database.

Shrink the Transaction log during an active transaction

Suppose we have a long-running active transaction for SQL Server database. Typically, we cannot shrink the transaction log in this case.

Running Query

If we have limited free space in the drive, we try to do it, but it does not work. In below screenshot, we can see that log file has free space inside it; however, if we try to shrink it does not work.

Shrink the Transaction log during an active transaction

The Log file continues to grow. We have a simple recovery model in this database. Once the query succeeded or fails then only, we can shrink the log.

Shrink effect during active trasaction

Let us perform the transaction log shrink in Accelerated Database Recovery enabled database. In the following screenshot, we have 24% free space in the log.

Shrink on Accelerated Database Recovery database

Shrink the log, and we can see it shrinked the transaction log. You may not notice much change in the log size as it is already consuming a small transaction log, but it is entirely possible to shrink the transaction log file.

Shrink effect during active trasaction on Accelerated Database Recovery Enabled database

Table of contents

Accelerated Database Recovery; Instant Rollback and Database Recovery
Accelerated Database Recovery and Long Running Transactions with Transaction Log Growth
Rajendra Gupta
Latest posts by Rajendra Gupta (see all)