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

By:   |   Updated: 2023-01-13   |   Comments   |   Related: More > 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:
availability group servers

Here are the properties of this AG group, where we can see it was using the Automatic seeding mode:

availability group properties

AOAG Not Synchronizing Issue

Go to the AOAG dashboard in SSMS to check the AOAG health:

availability group dashboard

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."

availability group warnings

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:

availability group join database

I checked the SQL Server error log on the primary replica and found this error message:

availability group error

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

availability group properties

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

backup database

After the full backup of database "File" finished, on the primary replica I set the Seeding mode for the secondary replica back to "Automatic":

availability group properties

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.

availability group dashboard
Next Steps





get scripts

next tip button



About the author
MSSQLTips author Vincent Jiang 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.

View all my tips


Article Last Updated: 2023-01-13

Comments For This Article

















get free sql tips
agree to terms