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)
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.
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.
|
1 |
select distinct(log_reuse_wait_desc) from sys.databases |
It can have the following values:
- ACTIVE_TRANSACTION
- LOG_BACKUP
- NOTHING
- REPLICATION
- Replication
- Database mirroring
- Log scan
- Always On Availability group
- XTP_CHECKPOINT
In the following image, you can see the basic overview of transaction log circular behaviour.
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
If the logs are truncated, you can see the transaction log structure as follows.
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.
|
1 2 |
select name,is_accelerated_database_recovery_on from sys.databases where name='SQLShackDemo' |
-
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.
1234USE [master]GOALTER DATABASE [SQLShackDemo] MODIFY FILE ( NAME = N'SQLShackDemo_log', MAXSIZE = 2097152KB )GO -
Put the database into the Simple Recovery Model. SQL Server should truncate the logs once a transaction is committed.
Before running the transaction, let us view the transaction log size using DMV sys.dm_db_log_stats
|
1 2 3 4 5 6 7 8 9 10 11 |
select db_NAME(database_id) dbname, recovery_model, current_vlf_size_mb, total_vlf_count, active_vlf_count, active_log_size_mb, log_truncation_holdup_reason, log_since_last_checkpoint_mb from sys.dm_db_log_Stats(DB_ID('SQLSHACKDEMO')) |
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
create Table TLogGrowth ( Logcapturetime time(0), DBname varchar(20), recovery_model varchar(20), current_vlf_size_mb int, total_vlf_count int, active_vlf_count int, active_log_size_mb int, log_truncation_holdup_reason varchar(20), log_since_last_checkpoint_mb int ) |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Use SQLShackDemo Go insert into TLogGrowth select convert(varchar, getdate(), 8), db_NAME(database_id) dbname, recovery_model, current_vlf_size_mb, total_vlf_count, active_vlf_count, total_log_size_mb, active_log_size_mb, log_truncation_holdup_reason, log_since_last_checkpoint_mb from sys.dm_db_log_Stats(DB_ID('SQLSHACKDEMO')) |
Specify the job frequency to every 30 seconds.
Now, run the following query to start the transaction. Please make sure SQL Server Agent should be running to capture transaction log growth.
|
1 2 3 4 5 6 7 8 9 10 11 |
Begin transaction Declare @Id int Set @Id = 1 While @Id <= 10000 Begin Insert Into tblSQLShackDemo(value) values (newid()) update tblSQLShackDemo set value=newid() Set @Id = @Id + 1 End |
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.
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.
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.
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.
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.
It is interesting to know transaction log usage for this database having Accelerated Database Recovery.
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
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.
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.
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.
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 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.
Table of contents
| Accelerated Database Recovery; Instant Rollback and Database Recovery |
| Accelerated Database Recovery and Long Running Transactions with Transaction Log Growth |
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023