Availability Group Backup Failures

James Anderson had a recent experience in which a database in an Availability Group failed to back up properly:

Last week, I received an alert that the percentage of transaction log in use on one of our production databases was increasing more than it should have been. I’ll refer to this database as DB1 from here on in. It had reached 18%, which is above the normal 5-10% we like to see it at. DB1 is in an Always On Availability Group so if the log usage jumps we jump.

The first thing I checked was that the log backups were running. We use Ola Hallengren’s maintenance solution to manage our backups. The backup jobs were running without error.

The next thing to check was the log_reuse_wait_desc column in sys.databases.

The highlighted row is the row for DB1. A log_reuse_wait of 2 means there were no changes to backup since the last log backup. If that were the case then why was my transaction log slowly filling like an unattended bath?

Read on to learn more.  James also has a link to a (closed/won’t-fix) Connect item.

Related Posts

Testing Backups With dbatools

Constantine Kokkinos shows off a dbatools cmdlet to test the last full backup: This: Defines a list of two servers (PowerShell lists are as easy as “”,””) Pipes them to the Test-DbaLastBackup command. Which then: Gathers information about the last full backups for all of your databases on that instance. Restores the backups to the Destination with […]

Read More

Synonyms And Availability Groups

Allen McGuire shows how to maintain synonyms between instances in an Availability Group: I’m a big fan of using synonyms to point to objects in other databases, but if you’ve tried to use them in conjunction with Availability Groups you may have encountered a slight issue: upon failover, the synonyms are no longer valid because […]

Read More

Categories

March 2016
MTWTFSS
« Feb Apr »
 123456
78910111213
14151617181920
21222324252627
28293031