Export (0) Print
Expand All
Expand Minimize

DROP AVAILABILITY GROUP (Transact-SQL)

Removes the specified availability group and all of its replicas. If a server instance that hosts one of the availability replicas is offline when you delete an availability group, after coming online, the server instance will drop the local availability replica. Dropping an availability group also deletes the associated availability group listener, if any.

Important note Important

If possible, remove the availability group only while connected to the server instance that hosts the primary replica. When the availability group is dropped from the primary replica, changes are allowed in the former primary databases (without high availability protection). Deleting an availability group from a secondary replica leaves the primary replica in the RESTORING state, and changes are not allowed on the databases.

For information about alternative ways to drop an availability group, see Remove an Availability Group (SQL Server).

Applies to: SQL Server (SQL Server 2012 through current version).

Topic link icon Transact-SQL Syntax Conventions

DROP AVAILABILITY GROUP group_name 
[ ; ]

group_name

Specifies the name of the availability group to be dropped.

  • Executing DROP AVAILABILITY GROUP requires that the AlwaysOn Availability Groups feature is enabled on the server instance. For more information, see Enable and Disable AlwaysOn Availability Groups (SQL Server).

  • DROP AVAILABILITY GROUP cannot be executed as part of batches or within transactions. Also, expressions and variables are not supported.

  • You can drop an availability group from any Windows Server Failover Clustering (WSFC) node that possesses the correct security credentials for the availability group. This enables you to delete an availability group when none of its availability replicas remain.

    Important note Important

    Avoid dropping an availability group when the Windows Server Failover Clustering (WSFC) cluster has no quorum. If you must drop an availability group while the cluster lacks quorum, the metadata availability group that is stored in the cluster is not removed. After the cluster regains quorum, you will need to drop the availability group again to remove it from the WSFC cluster.

  • On a secondary replica, DROP AVAILABILITY GROUP should only be used only for emergency purposes. This is because dropping an availability group takes the availability group offline. If you drop the availability group from a secondary replica, the primary replica cannot determine whether the OFFLINE state occurred because of quorum loss, a forced failover, or a DROP AVAILABILITY GROUP command. The primary replica transitions to the RESTORING state to prevent a possible split-brain situation. For more information, see How It Works: DROP AVAILABILITY GROUP Behaviors (CSS SQL Server Engineers blog).

Permissions

Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission. To drop an availability group that is not hosted by the local server instance you need CONTROL SERVER permission or CONTROL permission on that availability group.

The following example drops the AccountsAG availability group.

DROP AVAILABILITY GROUP AccountsAG;
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft