Suspend an Availability Database (SQL Server)
Applies To: SQL Server 2016 Preview
You can suspend an availability database in AlwaysOn Availability Groups by using SQL Server Management Studio, Transact-SQL, or PowerShell in SQL Server 2016. 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:
Effect of Suspend Command
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.
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 AlwaysOn 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 AlwaysOn 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.
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.
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 AlwaysOn 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
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 MyDb3 in the availability group MyAg on the server instance named Computer\Instance.
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
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