AG: database not synchronizing (suspend and resume)
SUSPEND/RESUME HADR statements to restart data movement for a database in an availability group, and removing a database with SET HADR OFF.
When a database in an availability group stops synchronising, suspending and then resuming its data movement is a common first step. The statements below use generic database names.
Suspend and resume data movement
ALTER DATABASE AppDb1 SET HADR SUSPEND
ALTER DATABASE AppDb4 SET HADR RESUME
ALTER DATABASE AppDb1 SET HADR SUSPEND
ALTER DATABASE AppDb1 SET HADR RESUME
ALTER DATABASE AppDb2 SET HADR SUSPEND
ALTER DATABASE AppDb2 SET HADR RESUME
ALTER DATABASE AppDb3 SET HADR SUSPEND
ALTER DATABASE AppDb3 SET HADR RESUME
Remove a database from the availability group
ALTER DATABASE AppDbName SET HADR OFF
Notes
- The first pair of statements suspends one database and resumes another. It is kept as written in the source; each suspend is normally followed by a resume of the same database.
SET HADR OFFremoves the database from the availability group on the replica where it is run.