How to suspend data movement in SQL Server Always On Availability Groups

The process to suspend data movement is the same from primary and secondary replica; however, you need to be connected to the appropriate instance.

Connect to the SQL in SSMS, go to Always On Availability Groups-> Availability Groups-> Availability Databases.

Right-click on the database for which we want to suspend the data movement.

Choose Suspend Data Movement and confirm. Or in T-SQL, on the replica concerned:

ALTER DATABASE [YourDB] SET HADR SUSPEND;

Suspending on the primary stops the log from being reused for every replica, so the primary’s transaction log grows until data movement is resumed (log_reuse_wait_desc = AVAILABILITY_REPLICA). Suspend for as short a time as possible.

Resume data movement in SQL Server Always On Availability Groups

To resume the data movement from the primary to the secondary replica, right-click on the availability group database and click on the Resume Data Movement.

ALTER DATABASE [YourDB] SET HADR RESUME;

Remove from Availability Group (don’t do)

Do not remove databases or availability groups as a fix. The Remove Availability Group dialogue deletes the whole AG, including the listener, for every database in it. Removing a single database (ALTER AVAILABILITY GROUP [YourAGName] REMOVE DATABASE [YourDB]) leaves the secondary copies in RESTORING state, and they must be reseeded to add it back. Both need approval.

From my SQL Server DBA handbook.