Symptoms
Assume that you use AlwaysOn Availability group in a Microsoft SQL Server 2012 or SQL Server 2014 database, and a large open active transaction exists and requires additional log space. When the log file can't grow for one of the following reasons, the transaction fails.
-
Lack of additional file space
-
The log file is configured not to grow
-
The log file has reached its configured maximum size
Additionally, you receive the following error message:
Error: 9002, Severity: 17, State: 9.
The transaction log for database '<database name>' is full due to 'LOG_BACKUP'.
After you run a log backup, you receive another 9002 error message:
Error: 9002, Severity: 17, State: 9.
The transaction log for database '<database name>' is full due to 'ACTIVE_TRANSACTION'.
After another log backup, you then receive another 9002 error message followed by a 5901 error message:
Error: 9002, Severity: 17, State: 9.
The transaction log for database '<database name>' is full due to 'AVAILABILITY_REPLICA'.
Could not write a checkpoint record in database <database name> because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files.
Error: 5901, Severity: 16, State: 1.
One or more recovery units belonging to database '<database name>' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.
When the subsequent checkpoint or log backups are then taken during the rollback of the transaction, you may receive the following error message:
Msg 3052, Level 16, State 1, Line 4
BACKUP LOG was unable to log updates for database '<database name>'. Subsequent log backups will be required to advance the backup point from '<LSN id 1>' to '<LSN id 2>' after log space is made available for logging them.
When you receive these messages, you're no longer able to submit any new transactions to the database, and you can’t grow the log file or add another log file.
Resolution
The issue was first fixed in the following cumulative update of SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. We recommend that you download and install the latest cumulative updates for SQL Server:
Workaround
You can use the following workaround to truncate the logs and resume activity.
-
Check each secondary replica to verify the secondary replica last_hardened_lsn (see sys.dm_hadr_database_replica_states) matches the primary replica last_hardened_lsn. You can do this by running the following query that is connected the primary replica instance
SELECT ags.name as AGGroupName,
ar.replica_server_name as InstanceName,
hars.role_desc,
db_name(drs.database_id)as DBName,
drs.last_hardened_lsn, drs.log_send_queue_size,
drs.synchronization_state_desc as SyncState,
ar.availability_mode_desc as SyncMode,
CASE drs.is_local WHEN 1 THEN drs.database_id ELSE NULL END as database_id
FROM sys.dm_hadr_database_replica_states drs
LEFT JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
LEFT JOIN sys.availability_groups ags ON ar.group_id = ags.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states hars
ON ar.group_id = hars.group_id and ar.replica_id = hars.replica_id
WHERE db_name(drs.database_id) = '<database name>' -
On the primary replica
-
Remove the database from the availability group.
-
Re-add the database to the availability group.
-
-
On each secondary replica
-
Re-add the database to the availability group.
-
By removing the database from the availability group, it will immediately truncate its logs and free up log space.
If the last_hardened_lsn on each secondary replica is identical to the primary replica, and no log backups are taken during the time of removing the database from the Availability Group and re-adding the database on each secondary, the secondary replica will successfully be re-added without any errors or having to restore log backups on the secondary.
If a secondary replica isn't current with the primary replica and you have to remove the database from the availability group before the secondary can catch up, that secondary replica may have to have log backups restored to catch it up before re-adding it to the availability group, or drop the database on the secondary replica and re-seed it with a full and transaction log database backup.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
