After power surge last night, I realized few availability databases (also known as a “database replica”) have an unhealthy data synchronization state. What I mean from unhealthy is that they have a status of “Not Synchronizing” in SQL Server Management Studio for both primary and all secondary availability group replicas (See below):

According to MSDN documentation here, this issue can be caused by the following:

• The availability replica might be disconnected.
• The data movement might be suspended.
• The database might not be accessible.
• There might be a temporary delay issue due to network latency or the load on the primary or secondary replica.

To investigate which one of those is relevant in my case, I tried to access the database in SQL Server Management Studio, but received the error similar to the one below:

The target database, ‘YourDatabase’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. (Microsoft SQL Server, Error: 976)

I also searched the SQL Server ErrorLogs for more information, and found following errors:

04/02/2014 12:06:23,spid37s,Unknown,AlwaysOn Availability Groups data movement for database ‘YourDatabase’ has been suspended for the following reason: “failover from partner” (Source ID 1; Source string: ‘SUSPEND_FROM_PARTNER’). To resume data movement on the database<c/> you will need to resume the database manually. For information about how to resume an availability database<c/> see SQL Server Books Online.

04/02/2014 12:06:23,spid37s,Unknown,The recovery LSN (969:3766:1) was identified for the database with ID 7. This is an informational message only. No user action is required.

04/02/2014 12:06:23,spid37s,Unknown,Error: 35285<c/> Severity: 16<c/> State: 1.

04/02/2014 12:06:23,spid37s,Unknown,AlwaysOn Availability Groups connection with primary database established for secondary database ‘YourDatabase’ on the availability replica with Replica ID: {f46ea26e-3162-4f49-97c6-b93946e78733}. This is an informational message only. No user action is required.

This information from ErrorLogs is quiet useful, as it tells me what’s wrong and how I can fix this issue. First, it tells that the attempt was made to connect to the primary availability replica. Then, it shows the “recovery LSN” for the database. And, finally, it tells that the Availability Groups data movement for database has been suspended, and we should manually resume the movement.

Solution: Resuming data movement on the database manually

To resume data movement on the database manually, I simply execute the following Transact-SQL statement on primary and all secondary replicas for the databases that are showing this status:

ALTER DATABASE [YourDatabase] SET HADR RESUME

After I issued this statement, the database is successfully synchronized.

Further Reading

• Data synchronization state of some availability database is not healthy
• ALTER DATABASE SET HADR (Transact-SQL)