Correct SQL Server Always On Availability Group Not Synchronized

How To Fix SQL Server Always On Availability Group Database Not Synchronized

By: Vincent Jiang | Updated: 2023-01-13 | Comments | Related: > Availability Groups

Problem

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.

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:

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.

Next Steps

About the author

Vincent Jiang is a senior DBA for SQL Server and Azure SQL DB with many years of experience, and has certificates for MCSE Infrastructure core and MCSA SQL 2016 administration, with rich experience in the Windows clusters, AOAG cluster, HA/DR for databases, and cloud migration.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-01-13