August 6, 2014 at 2:33 pm
I was working on a job to send me info each morning about database file free space and was noticing some odd things when looking at the log file VLFs for one of my databases in an AlwaysOn availability group.
When I run DBCC LOGINFO on the secondary replica for this database, I get what I expect and most VLFs have a status of 0 (indicating the VLFs are reusable or unused). When I run DBCC LOGINFO on the primary replica, all of the VLFs have a status of 2 (active or recoverable).
Since log backups on the secondary replica in AlwaysOn still truncate the log in the primary replica, I would expect that the VLFs in the primary replica would also be mostly in a reusable or unused state. My log file sizes are the same size on each server and my backups are completing successfully.
Does anyone know what might be causing the VLFs on the primary replica to have a status of 2 in DBCC LOGINFO when taking log backups from the secondary replica?
August 6, 2014 at 2:59 pm
Review this and any follow-on posts, being sure to review all the DMVs for various LSN values and states on primary and secondary: http://blogs.msdn.com/b/alwaysonpro/archive/2013/09/27/performing-transaction-log-backups-using-alwayson-availability-group-read-only-secondary-replicas-part-1.aspx
Hopefully you can figure it out from that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 6, 2014 at 3:52 pm
Thank you for the reply.
I read through the blog but I still don't think the explanations from it account for everything. The last_redone_lsn is one block behind the last_hardened_lsn so they redo process should be basically caught up.
The real hang-up for me is that when I run DBCC SQLPERF(LOGSPACE) on both servers I see that the percent of space used in the log file is at about %5 for both replicas but DBCC LOGINFO still shows that all VLFs on the primary replica have a status of 2 and only a few VLFs on the secondary replica have a status of 2. The last VLF that was created was created by a much older LSN so I'm pretty sure my VLFs are still being recycled even though they all have a status of 2 on the primary replica. The database has 111 VLFs that are roughly the same size so I would expect most VLFs to have a status of 0 since SQLPERF(LOGSPACE) indicates very low space usage in the log file.
August 6, 2014 at 7:41 pm
Perhaps what you are observing is simply an artifact of the AG normal operations? Perhaps BOL and blog posts just haven't caught up with that yet? I honestly haven't noticed that before but never looked either. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 7, 2014 at 3:09 am
friedrichsenm (8/6/2014)
I was working on a job to send me info each morning about database file free space and was noticing some odd things when looking at the log file VLFs for one of my databases in an AlwaysOn availability group.When I run DBCC LOGINFO on the secondary replica for this database, I get what I expect and most VLFs have a status of 0 (indicating the VLFs are reusable or unused). When I run DBCC LOGINFO on the primary replica, all of the VLFs have a status of 2 (active or recoverable).
Since log backups on the secondary replica in AlwaysOn still truncate the log in the primary replica, I would expect that the VLFs in the primary replica would also be mostly in a reusable or unused state. My log file sizes are the same size on each server and my backups are completing successfully.
Does anyone know what might be causing the VLFs on the primary replica to have a status of 2 in DBCC LOGINFO when taking log backups from the secondary replica?
What is the commit mode, synchronous or asynchronous?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 7, 2014 at 6:25 am
The commit mode is synchronous.
August 7, 2014 at 6:27 am
That's kind of what I'm thinking too at this point. Since DBCC LOGINFO is undocumented as well, I guess I can't rely on it to be able to handle how AlwaysOn handles log backups if Microsoft never intended for it to be used this way.
August 10, 2017 at 5:13 am
Resurrecting this as I've just had the exact same "concern"...
Looks like this "Lazy Log Truncation" is the problem with VLFs not being cleared down on the primary.
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply