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

SQL Server 2012 and later (recommended)

Run the following stored procedure. It automatically removes the database from the HA configuration, terminates all active sessions, and then drops the database:

-- Replace <database-name> with the name of the database to drop
EXEC sp_rds_drop_database '<database-name>';

SQL Server 2008

SQL Server 2008 does not support sp_rds_drop_database. Follow the steps below based on whether active sessions exist.

If active sessions exist (Cannot drop database "XX" because it is currently in use):

1. Get the process IDs (PIDs) of all active sessions in the database:

   USE master;
   EXEC sp_who;

   Look for rows where dbname matches your target database. Note the spid value for each active session.

2. Terminate each active session. Repeat for every session PID you identified:

   -- Replace <spid> with the session PID from the sp_who output
   KILL <spid>;

3. Remove database mirroring:

   -- Replace <database-name> with the name of the database to drop
   ALTER DATABASE <database-name> SET PARTNER OFF;

4. Drop the database:

   DROP DATABASE <database-name>;

If no active sessions exist (mirroring error only):

1. Remove database mirroring:

   ALTER DATABASE <database-name> SET PARTNER OFF;

2. Drop the database:

   DROP DATABASE <database-name>;