Do you frequently use the “log_reuse_wait_type_desc” column in the
sys.databases view? Have you ever seen the type of OLDEST_PAGE?
You are not the only one!!
Recently I was troubleshooting an issue with a database where the log was growing and the virtual logs would not clear after executing a transaction log backup. So I took a look at the value in
sys.databases.
We do use Transactional Replication and the database in question is the subscriber. So I checked and our distribution agent for this replication was not running.
Once I started the job, I performed my typical log cleanup script for this scenario:
CHECKPOINT; GO DBCC SHRINKFILE (db_log, 512); GO
On a normal day this would get my log file back to a smaller size and the space issue would be resolved.
This day however, guess what I discovered after I ran my trusty script? A logfile that was still filling up the drive!! What in the world, I have other things to do today.
So I checked the status of my “log_reuse_wait_type_desc” and guess what I found; “OLDEST_PAGE“.
OMG!! What is that. I have been working with SQL for 10+ years and never have I seen this as a status. Let me run my trusty script one more time, but first lets take a screenshot of this wait type.
After running a checkpoint and shrinkfile again, everything was back to normal.
Hooray…
Now back to this mythical log reuse wait type. Hey Google, what is “log_reuse_wait_type_desc” oldest page.
From the MS Docs we can see that they offer up some very wordy answer to a wait type 13 and want
me to go to two other links to get the full scoop. Transaction Log architecture, LSN, Image this, Operation that, my head hurts just typing about the topic. While Paul Randal loves talking about the Transaction log, it has always been a topic that goes right over my head.
However, the link about Checkpoints was very informative and brought some fuzzy thoughts I had on the subject in to a much clearer view. It appears there are some SQL Server levels settings and even database level settings since version 2012 that relate to checkpoints.
Lastly, I believe I caught this wait type while SQL was attempting to do its thing and get all the logs back to normal after the distribution agent got caught up with its work. So while it did give me something to research and learn about, I do not believe it was a critical error that would have caused major harm to my system.