Cannot shrink log file because the logical log file located at the end of the file is in use

As a database administrator, I seldom shrink my database, as it will cause index fragmentation in data file. But there are exceptions, such as log backup job failed due to disk full, and that was the case that I face today morning. The database joins an availability group, the daily log backup job was set to run on a secondary replica, but failed from Saturday. Transaction log file of that database grows to make the disk it resides nearly out of empty space. I manually ran a log backup first. Then I tried to run DBCC SHRINKFILE to shrink the log file, I got the following error message:

Cannot shrink log file because the logical log file located at the end of the file is in use.

This error is so common, especially if your database has publications of transactional replication and the log reader agent is not fast enough to process, but that's not the case this time. If it's a standalone or FCI sql server instance, as a quick fix, I will set the database to SIMPLE recovery model, shrink the log file, then set it back to FULL recovery, do a full backup and a log backup. But this database is in an availability group, we must remove the database from its availability group before setting it to SIMPLE recovery. So I further troubleshoot this case as below.

1. What is log reuse waiting on? We can check it by running a query on sys.databases DMV, which the log_reuse_wait columns tell you the reason.
USE [UserDB]
GO
DBCC LOGINFO; -- status 2 means the VLF is still in use
GO
SELECT [name], log_reuse_wait, log_reuse_wait_desc FROM sys.databases;
GO

2. I found the wait value was 13 (OLDEST_PAGE), which is caused by Indirect Checkpoint (default for newly created databases in SQL Server 2016), so I turn it off. Then the shrink log succeed.
USE master
GO
ALTER DATABASE [UserDB] SET TARGET_RECOVERY_TIME = 0 SECONDS WITH NO_WAIT;
GO
USE [UserDB]
GO
DBCC SHRINKFILE (N'UserDB_log' , 1024);
GO

Learning never stop for a SQL Server database administrator :)