SQL Server Exclusive Access Could Not Be Obtained

If you're a DBA with more than a few months experience you've seen the following error "Exclusive access could not be obtained because the database is in use", when trying to restore a database. The message is self explanatory; you can't restore a database because someone else is currently using it. Under most circumstances this is a fairly easy fix by querying sysprocesses and killing any users in the database you're trying to restore. Unfortunately sometimes you'll have a cross database query that doesn't show up when looking for the culprit. For example if in DB_A you're running an update statement against DB_B, and you query sysprocesses or any other DMV you won't see anyone accessing DB_B, because the the transaction is actually running within DB_A. Below is a dirty script to get at this info, it could probably be done a few different ways and much cleaner, but will usually allow you to find the offending transactions.

Declare @DBNametoKillInput varchar(100)

Declare @DBName varchar(110)

--Set DB Name you want to kill

set @DBNametoKillInput =
'email'

--Generate a like expression

set @DBName =
'%'
+ @DBNametoKillInput +
'%'

print @dbname

select
object_name(st.objectid),
*
from
sys.dm_exec_requests r

cross
apply sys.dm_exec_sql_text(r.sql_HANDLE) st

WHERE st.text LIKE @dbname