Understanding Transaction Log Truncation in SQL Server 2022

Question

Understanding Transaction Log Truncation in SQL Server 2022

Roberto Limongello 5

In an Azure SQL Server 2022 environment with multiple databases configured with FULL recovery mode, hourly log backups are taken, and full backups occur nightly.

According to the documentation, the expectation is that the log space usage should decrease to 0 or close to it after each log backup. However, observations show that the log space continues to grow over several days. While there are instances where the log is truncated, the specific circumstances leading to this behavior are unclear.

Standard troubleshooting steps have been taken, including monitoring the log_wait_reuse flag (which remains at NOTHING) and checking the recovery model interval (currently at 0), among other diagnostics.

What could be missing in this scenario?

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-01-21T22:43:59.68+00:00

Since you are taking backups yourself, I assume that you are not running Azure SQL Database, but possibly a Managed Instance, or even more likely SQL Server in an Azure VM. Can you confirm?

What are you using to monitor the log space?
Roberto Limongello 5 Reputation points

2025-01-22T09:04:38.6+00:00

Yes, SQL Server 2022 in Azure VM. The backups are performed by an Azure agent, which stores them in the Azure Vault. I monitor the backup status directly through the Azure platform.

To monitor the log space i'm running the query manually:

DBCC SQLPERF(logspace)
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-01-22T21:41:46.69+00:00

That is usually reliable. But there is a DMV these days, sys.dm_db_log_space_usage. I don't know much about the Azure Agent, but maybe there is some interplay that the old DBCC command misses, but which is captured by the DMV? (I am clutching at straws here.)
Roberto Limongello 5 Reputation points

2025-01-27T08:59:50.5766667+00:00

@Erland Sommarskog I checked the dm_db_log_space_usage but they are the same % of occupation.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-01-27T22:08:12.4266667+00:00

I don't really know what is going on. But without access to the machine it's difficult to run any diagnostics. Can you share some screenshots of the output, so we see for our own eyes?

2 answers

Your answer

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-01-21T22:43:59.68+00:00

Since you are taking backups yourself, I assume that you are not running Azure SQL Database, but possibly a Managed Instance, or even more likely SQL Server in an Azure VM. Can you confirm?

What are you using to monitor the log space?
Roberto Limongello 5 Reputation points

2025-01-22T09:04:38.6+00:00

Yes, SQL Server 2022 in Azure VM. The backups are performed by an Azure agent, which stores them in the Azure Vault. I monitor the backup status directly through the Azure platform.

To monitor the log space i'm running the query manually:

DBCC SQLPERF(logspace)
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-01-22T21:41:46.69+00:00

That is usually reliable. But there is a DMV these days, sys.dm_db_log_space_usage. I don't know much about the Azure Agent, but maybe there is some interplay that the old DBCC command misses, but which is captured by the DMV? (I am clutching at straws here.)
Roberto Limongello 5 Reputation points

2025-01-27T08:59:50.5766667+00:00

@Erland Sommarskog I checked the dm_db_log_space_usage but they are the same % of occupation.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-01-27T22:08:12.4266667+00:00

I don't really know what is going on. But without access to the machine it's difficult to run any diagnostics. Can you share some screenshots of the output, so we see for our own eyes?

Answer 1

Roberto Limongello 5

Follow up:

I opened a case to MS and after some exchanges I figure it out.

I was assuming for some reason that the VLF are all created equals in size. This is not always true apparently. In one case in particular I had 2/6 VLF occupying the 99% of the LDF meaning that the before the VLF inactivation would occur the usage would peak to 50% or more. This was more evident of course in small sized LDF.

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-01-31T22:10:47.2566667+00:00

Thanks for the update and taking the time to report back!

Answer 2

RahulRandive 10,486 Volunteer Moderator

Hi @Roberto Limongello

If the log space usage continues to grow despite regular log backups, it could be due to several factors:

In-Memory OLTP Checkpoints: If the databases have the In-Memory OLTP feature enabled, the transaction log might be waiting for an In-Memory OLTP checkpoint to occur.
Active Transactions: Long-running or uncommitted transactions can prevent log truncation.
Replication or CDC: If CDC or replication is enabled, it might be causing the log to grow.
Database Mirroring or Availability Groups: If the database is part of a mirroring setup or an availability group, synchronization issues might prevent log truncation.

Thank You!

Roberto Limongello 5 Reputation points

2025-01-23T08:14:11.29+00:00

Shouldn't the points you mentioned be reflected in the log_reuse_wait flag?

Additionally:

The recovery interval is set to 0 (default), which, according to Microsoft's documentation, should correspond to 60 seconds.

There are no such issues on the applications database. I have noticed some active transactions from sys.dm_tran_active_transactions that are not attached to any session or database. I initially considered this to be unrelated since the log_reuse_wait value across all databases is consistently 0 (meaning no waiting). However, I will restart the database and attempt to clear these transactions to check if they are somehow related. I doubt this is the case, as these transactions date back several months (11/24), which would have caused the log to grow indefinitely.

As for points 4 and 3, they are definitely not the cause.
NIKHILA NETHIKUNTA 4,600 Reputation points Microsoft External Staff

2025-01-24T01:09:57.4166667+00:00

@Roberto Limongello
Apologies that your issue did not get resolved.
Can you please let me know if you tried to restart the database? Did your issue get resolved?

Please let us know if you need any further help.
Roberto Limongello 5 Reputation points

2025-01-24T15:01:57.18+00:00

I have restarted the SQL Service, but the sys.dm_tran_active_transactions view still shows the same orphaned transaction from 11-2024. It seems like another bug on top of the existing one, as running DBCC OPENTRAN on every database does not show these transactions. For some reason, they were not cleared from sys.dm_tran_active_transactions.

Unfortunately, I can't remove them manually, as attempting to do so results in the error: 'Ad hoc updates to system catalogs are not allowed.'

At this point, it's unclear whether these records are contributing to the log issue or they're just independent.
Roberto Limongello 5 Reputation points

2025-01-24T15:02:42.4433333+00:00

I have restarted the SQL Service, but the sys.dm_tran_active_transactions view still shows the same orphaned transaction from 11-2024. It seems like another bug on top of the existing one, as running DBCC OPENTRAN on every database does not show these transactions. For some reason, they were not cleared from sys.dm_tran_active_transactions.

Unfortunately, I can't remove them manually, as attempting to do so results in the error: 'Ad hoc updates to system catalogs are not allowed.'

At this point, it's unclear whether these records are contributing to the log issue.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-01-24T18:10:15.7266667+00:00

I also see a couple of transactions on my very idle instance of SQL 2022, but they seem be some internal benign stuff. This is what I see:

As you can see have been active for a some days, probably since I last booted by laptop. I looked in the docs, and I found that transaction_state = 2 means read-only transaction.

Now, I don't know what you are seeing, but if you see the same as I do, I don't think that is the answer of the problem.

From one thing to another, have you looked at sys.dm_db_log_space_usage yet to see if reports something else from DBCC SQLPERF(LOGSPACE)?

Share via

Understanding Transaction Log Truncation in SQL Server 2022

2 answers

Your answer