Tag Archives: SQL: Cannot drop database XXX because it is currently in use

[Solved] SQL Server Error: Cannot drop database XXX because it is currently in use

I encountered this problem when using pymssql to connect to SQL Server.

pymssql.OperationalError: (3702, b'Cannot drop database "XXX" because it is currently in use.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n')
1
Programmer:

 	cursor = conn.cursor()
    conn.autocommit(True)
    cursor.execute('CREATE DATABASE XXX ON (NAME=\'XXX_Data\', FILENAME=\'{}\\XXX.mdf\', SIZE=5 MB, MAXSIZE=50 MB, FILEGROWTH=5%) LOG ON (NAME=\'XXX_Log\',FILENAME=\'{}\\XXX_Log.ldf\',SIZE=1 MB,MAXSIZE=5 MB,FILEGROWTH=1 MB)'.format(dir, dir))
    cursor.execute('USE XXX CREATE TABLE xxx ( xxxxxxx )')
    cursor.execute('DROP TABLE xxx')
    cursor.execute('USE XXX DROP DATABASE XXX')

I turned on autocommit before operating the database, and then found that if I performed other operations on the database between the CREATE DATABASE and DROP DATABASE statements, the above error would occur when DROP DATABASE.
After checking the relevant information, I found that the reason is that the database itself is used incorrectly when deleting the database: "USE XXX", in order to delete successfully, you need to change the USE to "USE MASTER": cursor.execute(cursor)

cursor.execute('USE MASTER DROP DATABASE XXX')
1
Then the deletion is successful.

Another method on the Internet is to add the following statement to roll back the database to the initial state, the actual test did not affect me, the reason is unknown.

use master
go
alter database database_name set single_user with rollback immediate 
 

Moved online, effective