We're having an issue with an AG where the the log backup does not appear to truncate the log. symptoms

• Run full backup
• Run transaction log backup
• DBCC Loginfo shows all VLFs with a status of 2
• sys.databases.log_reuse_wait_desc says LOG_BACKUP
• OPENTRANS indicates no open trans

All backups are being run on the primary.  Any ideas?

thanks

Peter,

Are you seeing the Tlog files in the destination folder? If so it means the log is backing up.  Check how much space log is using out of allocated space.

Thanks.

Yes the tx log backupss are there. 

DBCC SQLPERF(Logspace) shows 34% of the file is used. DBCC LOGINO however shows that all of the VLFs are in a status of 2.

Hmm..That means some process is happening in the log.  How about the secondary replica database?  Is it synchronizing? 

Its normal for the log_reuse_wait to remain at "LOG_BACKUP" even after you took a transactional log backup. It will switch back to "LOG_BACKUP" at the first checkpoint execution after the backup completion.

Are you aware that the log files won't shrink automatically? Despite the fact that LOGINFO shows all VLFs status as 2, what happens when you try to shrink the file? Does it release the available space back to the OS? Or does it remain stuck at a certain file usage percentage despite shrinking efforts?

No I cannot shrink the file.

In SSMS all primary databases are synchronized and secondaries are all synchronizing. Dashboard the same

Looking at some DMVs

sys.dm_hadr_database_replica_states:  all data bases have a synchronization status of healthy on both servers

sys.dm_hadr_availability_replica_states: both replicas have a synchronization status of healthy

sys.dm_hadr_availability_group_states:

• on primary the primary has  a status of healthy
• on secondary the secondary has a status of unhealthy

Confused as to why the lower level states are all healthy but the rollup is not

On all secondary replicas in AG you need to check if your databases are in sync with the primary replica.
All databases icons in AG on secondaries should be green.

If they are grey then right-click mouse on every database and click "resume data movement". For some reason databases(s) on the one or all secondary replicas out of sync.

Regards,
Sergei.

All of  the databaees are green

Hmm, can you backup the log on the secondary? Do you see the status has changed?

http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/06/27/lazy-log-truncation-clearing-of-sql-transaction-log-vlf-status-deferred.aspx

 

After another log backup (primary) we were down to 1% log utilization and relog_reuse_wait_desc =nothing. All VLFs are a status of 2.

The Lazy Log Truncation sounds like what we are seeing.   I'm not quite sure what "Lazy Log Truncation marks the VLFs for truncation, but it does not actually truncate the log unless those VLFs are necessary" really means. Maybe it truncates the a when it needs  it for an new transaction or there is  some other criteria.

At any rate it makes it really difficult to shrink a log in an availability group. The log had grown large due to a backup issue. We may remove it from the AG to shrink it and then add it back in.

• Proposed as answer by Lydia ZhangMicrosoft contingent staff, Moderator Tuesday, September 16, 2014 11:37 AM
• Marked as answer by Lydia ZhangMicrosoft contingent staff, Moderator Monday, September 22, 2014 1:12 AM

I am seeing this same behavior on my Availability Groups. If I accept the default availability group backup preferences (prefer secondary), the transaction log is backed up on the secondary, but the VLF status doesn't change from 2 to 0. Despite the log only being 0.1% used, I am not able to shrink the log file either.

If I want to shrink the log file, I have to change the backup preferences to occur on the primary. After which I can backup the transaction log and the VLF status is changed from 2 to 0. At which point I can shrink the log file size.

Though it's not ideal, it's better than removing the database from the AG and adding it back after shrinking the log.