While trying to dismount a database I ran into the error: Cannot detach the database ‘Database Name’ because it is currently in use. (Microsoft SQL Server, Error: 3703).
This almost always happens due to an open connection which you can drop during the detachment process, but I like to exclude that option. This helps determine if the database still has connections to anything. There are two queries you can run that will tell you what is connected to the database. First run the below query.
select * from sys.sysprocesses where dbid = db_id('Database Name')
The returned results will display a host name, and a login name. Using the two you can determine if the connection is initiated locally, or externally. If externally, simply go to the server that is connecting to it and remove any User or System DSNs. If none are listed the database is likely configured inside of an application. Refer to your documentation for whatever application resides on the server in order to remove the database connections.
If you find that the connection was initiated locally, you will need to run the below query. This query provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.
sp_who
Using the status, loginname, hostname, dbname, and cmd fields you can likely determine what process is locking the database locally. In almost all cases I’ve seen it is due to a backup, transaction log shipping, or some other type of SQL related task.
If you’ve managed to track down the culprit and removed the connection to the database you should now be able to proceed with detaching the database without dropping open connections. For more information about SQL sp_ commands click HERE.
