What do I do if databases cannot be directly deleted from an ApsaraDB RDS for SQL Server instance? - ApsaraDB RDS

This topic describes how to resolve the issue that databases cannot be directly deleted from an ApsaraDB RDS for SQL Server instance.

Note

Take note of the following items:

Before you perform high-risk operations, such as modifying the configurations or data of Alibaba Cloud instances, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
Before you modify the configurations or data of an instance, such as an Elastic Compute Service (ECS) instance or an RDS instance, we recommend that you create snapshots or enable backup for the instance. For example, you can enable log backup for an RDS instance.
If you granted permissions on sensitive information or submitted sensitive information in the Alibaba Cloud Management Console, we recommend that you modify the sensitive information at the earliest opportunity. Sensitive information includes usernames and passwords.

Problem description

When you delete a database from an RDS instance, one of the following errors occurs:

No sessions exist in a database: If you execute the DROP DATABASE statement to delete the database from an RDS instance that runs SQL Server 2008, SQL Server 2012, or SQL Server 2016 and uses the mirroring technology to implement primary/secondary synchronization, the following error occurs:
```
The database 'XX' is enabled for database mirroring. Database mirroring must be removed before you drop the database.
```
No sessions exist in a database: If you execute the DROP DATABASE statement to delete the database from an RDS instance that runs SQL Server 2017 and uses AlwaysOn to implement primary/secondary synchronization, the following error occurs:
```
The database 'XX' is currently joined to an availability group. Before you can drop the database, you need to remove it from the availability group.
```
Sessions exist in a database: If you execute the DROP DATABASE statement to delete the database from an RDS instance that runs SQL Server 2008, SQL Server 2012, SQL Server 2016, or SQL Server 2017, the following error occurs:
```
Cannot drop database "XX" because it is currently in use
```

Causes

The RDS instance runs RDS High-availability Edition and uses a mirroring- or AlwaysOn-based high availability (HA) architecture. If you want to delete a database from the RDS instance, you must remove database mirroring or AlwaysOn availability groups. If sessions exist in the required database and you want to delete the database, you must run the kill command to terminate the sessions before you delete the database.

Solutions

Note

The solutions are applicable only to RDS instances that run SQL Server on RDS High-availability Edition. For more information, see RDS High-availability Edition.

SQL Server 2012 and later

Log on to the required database and execute the following statement to delete the database:

EXEC sp_rds_drop_database '[$Database]';

Note

[$Database] specifies the name of the database that you want to delete.
The preceding statement is used to remove the HA architecture, terminate sessions in the database, and then delete the database.

SQL Server 2008

Log on to the required database and perform the following operations:

If the Cannot drop database "XX" because it is currently in use error message is displayed, run the kill command to terminate the session in the database and then delete the database.
1. Execute the following statement to obtain the process ID (PID) of the session in the database that you want to delete:
```
USE master;
EXEC sp_who;
```
2. Run the following command to terminate the session:
```
kill [$Spid];
```
  Note
  [$Spid] specifies the required PID of the session in the database that you want to delete.
3. Execute the following statement to directly remove database mirroring:
```
ALTER DATABASE [$Database] SET PARTNER OFF;
```
  Execute the following statement to delete the database:
```
DROP database [$Database];
```
If the Cannot drop database "XX" because it is currently in use error message is not displayed, execute the following statement to directly remove database mirroring.
```
ALTER DATABASE [$Database] SET PARTNER OFF;
```
Execute the following statement to delete the database:
```
DROP database [$Database];
```

References

For more information about how to delete a database in the ApsaraDB RDS console or by calling an API operation, see Delete a database in the ApsaraDB RDS console or DeleteDatabase.