How to drop SQL Server database currently in use and in Single user mode

To identify the session currently using the database you can try

SELECT request_session_id
FROM   sys.dm_tran_locks
WHERE  resource_database_id = DB_ID('G_MAIN_DE') 

This built-in stored procedure shows all connections to a database

exec sp_who2

And this query kills a connection to the database, for example connection SPID #53

exec kill 53

The easiest thing to so is use SSMS Activity Monitor (Right-click server/Activity Monitor/Processes). Sort by Database. Kill whatever process has a hold onthe DB. You might also want to issue the kill command right before you set the DB to single-user and do the drop.

Tags:

Sql Server