A few days ago a customer called me to ask if I could help him with a problem with the log file.
He tried to shrink it, but the system wasn’t doing anything and he didn’t know why.
With the column ‘log_reuse_wait_desc’ you can find it out.
When I asked him what was in the ‘log_reuse_wait_desc’ column for the database, there was silence at first, then the question quickly arose as to where the column could be found at all.
But let’s start at the beginning.
I arranged a session with the customer and asked him to show me what he had in mind.
He tried to reduce the log file to the smallest possible size with the following command:

The output is successfull and nothing has happened.
But why?
I quickly found out that this was a database located in an AlwaysOn group.
Some time before his call, an application had made large changes to the database by mistake. This caused the log file to grow enormously. From there also the desire to restore the old size.
In the well-known system table sys.databases there is a column with the name ‘log_reuse_wait_desc’ which tells us why it does not work.
So I executed a query based on the name of the database and the column:

The reuse_wait ‘AVAILABILITY_REPLICA’ was recognizable.
This means that the log file is replicating the transactions that have been carried out. Until this has happened, the log file cannot be manipulated.
We therefore had to wait until all transactions had been replicated and the status had changed:

On the status ‘ACTIVE_TRANSACTION’, we are already able to shrink the previous enlargement.
Then we could reduce the size of the file.
Are there any other log waits?
The following are listed in the official Microsoft documentation:
- NOTHING
- CHECKPOINT
- LOG_BACKUP
- ACTIVE_BACKUP_OR_RESTORE
- ACTIVE_TRANSACTION
- DATABASE_MIRRORING
- REPLICATION
- DATABASE_SNAPSHOT_CREATION
- LOG_SCAN
- AVAILABILITY_REPLICA
- OLDEST_PAGE
- XTP_CHECKPOINT
- SLOG_SCAN
Customers often ask me exactly the opposite. Why can’t I reduce the size of the log file even though I have only made small changes to the database?
The main problem lies with the VLFs.
When the transaction log is not growing, and you’re taking regular log backups, but the log_reuse_wait_desc stays at LOG_BACKUP, the reason is that the previous log backup did not clear any Virtual Log Files (VLFs).
How can this occur?
Consider a database with minimal insert, update, delete, or DDL activity. In this scenario, only a few log records are generated between your regular log backups, and they all reside in the same Virtual Log File (VLF). When the next log backup runs, it backs up these few log records but cannot clear the current VLF, preventing the log_reuse_wait_desc from changing. Once enough changes are made in the database to fill the current VLF and activate the next one, the subsequent log backup should be able to clear the previous VLF, causing the log_reuse_wait_desc to change to NOTHING. However, if the next log backup cannot clear the current VLF, the log_reuse_wait_desc will revert to LOG_BACKUP again.
If you want to find out more about VFLs, take a look at my colleague’s blog entry. He has covered the topic in great detail: