Problem
We had an issue with a SQL Server database in an Always On Availability Group (AOAG) where it was not synchronizing the secondary replica even though it was using the “Synchronous commit” Availability Mode for the secondary replica. This article explains how to troubleshoot and resolve:
“In this availability group, at least one secondary replica has a not synchronizing synchronization state and is not receiving data from the primary replica.”
Solution
Below is information about the AOAG that is used for this article:
- AOAG name: AG2
- AOAG servers:
Here are the properties of this AG group, where we can see it was using the Automatic seeding mode:
SQL Server Always On Not Synchronizing Issue
Go to the AOAG dashboard in SSMS to check the AOAG health:
I found that database “File” is “Not Synchronizing” on the secondary replica (uxprsqlcc02\A22P2) and showed warnings. This database is healthy on the primary replica (uxprsqlcc01\A22P2). We monitored this issue for more than 1 hour and it did not resolve itself. Since this database is still in use, we did our troubleshooting very carefully.
I click on “Warnings(2)” to show the details of the warnings and found the following message: “Secondary database is not joined.”
Referring to this Microsoft article, we should be able to just rejoin the database on the secondary replica to resolve the issue.
When I manually rejoined this DB on the secondary replica (uxprsqlcc02\A22P2) I got this error:
I checked the SQL Server error log on the primary replica and found this error message:
This message was “The remote copy of database “File” has not had enough log backups applied to roll forward all of its files to a common point in time.” Therefore, a new log backup of database “File” can provide the latest common point in time for both the primary and secondary replicas.
I changed the Seeding mode for this secondary replica (uxprsqlcc02\A22P2) to “Manual”:
Then I dropped database “File” manually on the secondary replica (uxprsqlcc02\A22P2) by executing the following SQL script on uxprsqlcc02\A22P2:
USE [master]
GO
DROP DATABASE [File]
GOAfter database “File” was dropped on the secondary replica (uxprsqlcc02\A22P2), I created a full backup of database “File” on the primary replica (uxprsqlcc01\A22P2):
After the full backup of database “File” finished, on the primary replica I set the Seeding mode for the secondary replica back to “Automatic”:
The database “File” was synchronized successfully on the secondary replica (uxprsqlcc02\A22P2) after about 5 minutes as shown below. The time will vary depending on the size of the database.
Next Steps
