You can suspend an availability database in Always On availability groups by using SQL Server Management Studio, Transact-SQL, or PowerShell in SQL Server 2017. Note that a suspend command needs to be issued on the server instance that hosts the database to be suspended or resumed.
The effect of a suspend command depends on whether you suspend a secondary database or a primary database, as follows:
|Suspended Database||Effect of Suspend Command|
|Secondary database||Only the local secondary database is suspended and its synchronization state becomes NOT SYNCHRONIZING. Other secondary databases are not affected. The suspended database stops receiving and applying data (log records) and begins to fall behind the primary database. Existing connections on the readable secondary remain usable. New connections to the suspended database on the readable secondary are not allowed until data movement is resumed.
The primary database remains available. If you suspend each of the corresponding secondary databases, the primary database runs exposed.
** Important *\* While a secondary database is suspended, the send queue of the corresponding primary database will accumulate unsent transaction log records. Connections to the secondary replica return data that was available at the time the data movement was suspended.
|Primary database||The primary database stops data movement to every connected secondary database. The primary database continues running, in an exposed mode. The primary database remains available to clients, and existing connections on a readable secondary remain usable and new connections can be made.|
Suspending an Always On secondary database does not directly affect the availability of the primary database. However, suspending a secondary database can impact redundancy and failover capabilities for the primary database. This is in contrast to database mirroring, where the mirroring state is suspended on both the mirror database and the principal database. Suspending an Always On primary database suspends data movement on all the corresponding secondary databases, and redundancy and failover capabilities cease for that database until the primary database is resumed.
Before you begin:
To suspend a database, using:
Follow up: Avoiding a Full Transaction Log
Before You Begin
Limitations and Restrictions
A SUSPEND command returns as soon as it has been accepted by the replica that hosts the target database, but actually suspending the database occurs asynchronously.
You must be connected to the server instance that hosts the database that you want to suspend. To suspend a primary database and the corresponding secondary databases, connect to the server instance that hosts the primary replica. To suspend a secondary database while leaving the primary database available, connect to the secondary replica.
During bottlenecks, suspending one or more secondary databases briefly might be useful to improve performance temporarily on the primary replica. As long as a secondary database remains suspended, the transaction log of the corresponding primary database cannot be truncated. This causes log records to accumulate on the primary database. Therefore, we recommend that you resume, or remove, a suspended secondary database quickly. For more information, see Follow up: Avoiding a Full Transaction Log, later in this topic.
Requires ALTER permission on the database.
Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.
Using SQL Server Management Studio
To suspend a database
In Object Explorer, connect to the server instance that hosts the availability replica on which you want to suspend a database, and expand the server tree. For more information, see Prerequisites, earlier in this topic.
Expand the Always On High Availability node and the Availability Groups node.
Expand the availability group.
Expand the Availability Databases node, right-click the database, and click Suspend Data Movement.
In the Suspend Data Movement dialog box, click OK.
Object Explorer indicates that the database is suspended by changing the database icon to display a pause indicator.
To suspend additional databases on this replica location, repeat steps 4 and 5 for each database.
To suspend a database
Connect to the server instance that hosts the replica whose database you want to suspend. For more information, see Prerequisites, earlier in this topic.
Suspend the database by using the following ALTER DATABASEstatement:
ALTER DATABASE database_name SET HADR SUSPEND
To suspend a database
Change directory (cd) to the server instance that hosts the replica whose database you want to suspend. For more information, see Prerequisites, earlier in this topic.
Use the Suspend-SqlAvailabilityDatabase cmdlet to suspend the availability group.
For example, the following command suspends data synchronization for the availability database
MyDb3in the availability group
MyAgon the server instance named
Suspend-SqlAvailabilityDatabase ` -Path SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups\MyAg\Databases\MyDb3
To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL Server PowerShell environment. For more information, see Get Help SQL Server PowerShell.
To set up and use the SQL Server PowerShell provider
Follow Up: Avoiding a Full Transaction Log
Normally, when an automatic checkpoint is performed on a database, its transaction log is truncated to that checkpoint after the next log backup. However, while a secondary database is suspended, all of the current log records remain active on the primary database. If the transaction log fills up (either because it reaches its maximum size or the server instance runs out of space), the database cannot perform any more updates.
To avoid this problem, you should do one of the following:
Add more log space for the primary database.
Resume the secondary database before the log fills up. For more information, see Resume an Availability Database (SQL Server).
Remove the secondary database. For more information, see Remove a Secondary Database from an Availability Group (SQL Server).
To troubleshoot a full transaction log