Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

Focus on OLDEST_PAGE as a factor that can delay LOG truncation

09 December,2021 by Tom Collins

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

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Focus on OLDEST_PAGE as a factor that can delay LOG truncation | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer