Recently one of our synchronous AlwaysOn Groups stopped replicating data and went into a Non Synchronising state. In the dashboard on the primary everything was showing as OK, but on the secondary all the DBs had a little red x against them.

On the primary the log was flooded with an error for one of the databases being:

The transaction log for database 'xxxx' is full due to 'AVAILABILITY_REPLICA'.

In the dashboard I looked at the redo queue and it was larger than usual, however the more interesting thing was that when I ran this query:

SELECT db_name(database_id) as DBName,
    session_id FROM sys.dm_exec_requests
    WHERE command = 'DB STARTUP'
    ORDER BY db_name(database_id)

No redo threads were there for the database in question.

We tried resuming data movement but that did not work, neither did a reboot of the passive node. We ended up having to completely remove all databases from that group, then remove the replica itself, re-add the replica and re-add the databases back into the group.

Has anyone seen this before or have any idea about troubleshooting root cause here, and if there is a way to restart redo threads? Would that have helped?

Also I checked and there was no blocking at the time of the redo thread, nor is this read only so no reports were running. In fact, there was no redo thread running at all on the secondary. Have you seen that before?

I am taking log backups. Autogrowth is enabled. Running SQL Server 2012 SP2 (no CU).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.