How To Fix SQL Server Always On Availability Group Database Not Synchronized
We had an issue with a 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. Ian this article I will walk through the process of troubleshooting this issue and how this was resolved.
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:
AOAG 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, https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/secondary-database-is-not-joined?view=sql-server-ver16, 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] GOGO
After 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":
After about 5 minutes, database "File" was synchronized successfully on the secondary replica (uxprsqlcc02\A22P2) as shown below. The time will vary depending on the size of the database.
- Join a secondary database to an Always On Availability Group
- Use the Always On Availability Group dashboard (SQL Server Management Studio)
- Differences between availability modes for an Always On Availability Group
- More SQL Server Availability Group Tips
About the author
View all my tips
Article Last Updated: 2023-01-13