SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Availability databases in unhealthy data synchronization state (Error: 35285, Severity: 16, State: 1.)

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):

avalability_group_011

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


Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Comments

Leave a comment on the original post [basitaalishan.com, opens in a new window]

Loading comments...