Correct SQL Server Always On Availability Group Not Synchronizing

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]
GO

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

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

• 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

Vincent Jiang

Vincent Jiang is skilled at SQL Server database management and has much experience using PowerShell scripts and T-SQL scripts to do automatic operation and management of database, including instances deployment, daily operations (backup/monitoring/jobs/auditing), troubleshooting, AOAG, DB tuning, performance management, Server-Instance-Database lifecycle management, database migration, database DevOps (Azure DevOps), and elastic jobs for Azure SQL Database. His wife Cherry is a great supporter of his technical writing, thanks, Cherry! In addition to studying SQL technology, Vincent also likes to travel and enjoy leisure time with his family.