Focus on OLDEST_PAGE as a factor that can delay LOG truncation

SQL Server Log truncation deletes inactive Virtual Log Files (VLF) from the SQL Server database transaction log . The Log truncation process frees  space in the logical log for reuse by the Physical transaction log. If no truncation occurs , eventually it will fill all the disk space allocated to physical log files.

SQL Server Log truncation can be delayed for a range of different reasons.A good starting point is to  query the sys.databases log_reuse_wait and log_reuse_wait_desc columns. This will supply different waits describing the reason for a delay 

One of the wait types that can appear is the  OLDEST_PAGE. 

From the Microsoft docs : "If a database is configured to use indirect checkpoints, the oldest page on the database might be older than the checkpoint log sequence number (LSN). In this case, the oldest page can delay log truncation. (All recovery models)"

An indirect checkpoint refers to a feature introduced in SQL Server 2012 that allows a database to be configured to a specific target recovery time - as opposed to an automatic checkpoint.  A typical use case  would be an online transactional database configured to maintain dirty pages below a certain thresholds, so in the event of performance issues a db recovery occurs within the target recovery time. i.e increased control .

From SQL Server 2016 the default value is 1 minute - whereason the older versions it was set at 0 - meaning the database is using automatic checkpoints. 

One method to  fix the issue - if you have a database which is configured to use indirect checkpoints is to :

1) Create CHECKPOINT manually 

2) BACKUP the transaction log again

Read more on checkpoint, virtual log files 

Database has more than 1000 virtual log files which is excessive – informational message 9017

Recovery is writing a checkpoint in database

Is a LOG BACKUP allowed on a SIMPLE RECOVERY database

How to find sql transactions with large logs