Today, during an application release, one of our DBAs (who is a novice) was trying to do a database restore from a backup copy. An error kept appearing during his attempts to perform the store stating “Exclusive access could not be obtained because the database is in use.” He tried several different ways to “kill” these current db connections.

After googling somewhat, there was a forum post about killing all connections by just detaching the database. One would perform this via the SQL Server Management Studio.

First, right-click the database name, and select Tasks > Detach… Once the dialog window pops up, you can see in Message column the number of active connections to the database. Upon clicking the link, it will display the Activity Monitor which includes info on db users currently connecting and their statuses (and their respective process IDs).

Within the Activity Monitor, you can can right-click each process and kill them manually. This worked for us since there were less than a handful connections active. If there were a ton, then this may not have been the ideal solution.

I also did come across another way that used SQL to issue commands that would place the db into single user mode, thus killing the other connections and providing exclusive access to the db.

Below, is a set of commands a DBA can issue that may be another solution. (I still have to try this.)

ALTER DATABASE [Test4] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
OR
ALTER DATABASE [Test4] SET SINGLE_USER WITH ROLLBACK AFTER 30
OR
ALTER DATABASE [Test4] SET SINGLE_USER WITH NO_WAIT

• WITH ROLLBACK IMMEDIATE – this option doesn’t wait for transactions to complete it just begins rolling back all open transactions
• WITH ROLLBACK AFTER nnn – this option will rollback all open transactions after waiting nnn seconds for the open transactions to complete. In our example we are specifying that the process should wait 30 seconds before rolling back any open transactions.
• WITH NO_WAIT – this option will only set the database to single user mode if all transactions have been completed. It waits for a specified period of time and if the transactions are not complete the process will fail. This is the cleanest approach, because it doesn’t rollback any transactions, but it will not always work if there are open transactions.