I run into an interesting issue with an Availability Group database recently. I was running a data load against a database in an Always On Availability group database in SQL Server 2012. The load was SSIS package migrating data from staging database when the package fell over with the following error
“The transaction log for database DBName is full due to 'AVAILABILITY_REPLICA'”
This was interesting error. Initially I thought my log had filled up all the space on the disk but after examining the drive on the server, looking at the free space in the log and running a
SELECT * FROM sys.databases
I found that this was not the case.
My query returned the following in the log_reuse_wait_desc
log_reuse_wait_desc
AVAILABILITY_REPLICA
The SQL Server documentation gives the following explanation of this log_reuse_wait_desc
“9 = An AlwaysOn Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database. “
For more information see http://msdn.microsoft.com/en-us/library/ms178534.aspx
My AlwaysOn setup had involved three instances, one primary two secondary’s one setup with synchronous commit mode and automatic failover and the other asynchronous with manual failover. The synchronous commit mode with automatic failover had fallen out of sync and was marked as ‘Not Synchronising.’ The drive holding the log of this secondary database still had plenty space, the log itself still had plenty of space, there was no need for it to grow
This was pain for me as the database was going to end up quite large and I didn’t want to re-initialise availability group therefore I didn’t want to remove the database from the availability to run the load and then re-initialize after it had completed. Instead I changed the secondary replica using synchronous commit mode to use asynchronous commit with manual failover, this meant the primary didn’t have to wait for the secondary replica to harden its log before continuing, hopefully so the load would complete without further error. It wouldn’t have to wait for the transactions to harden against the secondary. When the load had finished and the secondary had caught up synchronizing I was able to switch back to synchronous commit and configure automatic failover.
For the latest SQL Server 2012 Training visit
