Fix: Cannot detach the database ‘DBName’ because it is currently in use. (Microsoft SQL Server, Error: 3703)

Cannot detach the database ‘DBName’ because it is currently in use. (Microsoft SQL Server, Error: 3703)

When you detach a SQL DB via script/programmatic way you notice SQL fails to detach the DB with error indicating that the DB is still in use.  The same DB detach just works fine when you manually detach the DB before attempting to do it via scripted way.  Once scripting fails, even manual detach fails including restarting the SQL server instance service fails to get the DB released.

This scenario particularly exists while you are copying/duplicating/restoring the DB on the SQL server instance.

Error:

TITLE: Microsoft SQL Server Management Studio

——————————

Detach database failed for Server ‘TESTSRV2008R2MyDB’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0038+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Detach+database+Server&LinkId=20476

——————————

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

Cannot detach the database ‘MyDB’ because it is currently in use. (Microsoft SQL Server, Error: 3703)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500&EvtSrc=MSSQLServer&EvtID=3703&LinkId=20476

——————————

BUTTONS:

OK

——————————

Note: Cannot detach the database ‘DBName’ because it is currently in use. (Microsoft SQL Server, Error: 3703)

 

Cause and Troubleshooting:

You don’t understand which process is holding the DB in-use. Further to that killing the respective accessing DB processes even won’t help you.  Forcing DB detach/delete with ‘end/close existing connections’ leaves DB in Single user mode.

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame

from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid

where d.name like ‘%mydb%’

go

kill 53

go

Fix:

The fix is to make the DB offline with ‘SET OFFLINE WITH ROLLBACK IMMEDIATE’ setting so that the DB will turn offline immediately and then Detach DB will work fine.  When you attach the DB you can make the DB Online.

ALTER DATABASE <DBName> SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE [Database Name] SET ONLINE

References:

1. Remove SQL Server database from single-user mode
2. How to kill all sessions that have open connection in a SQL Server Database? « SQLDBPOOL.COM