Exclusive access could not be obtained because the database is in use

Well, after a few days of actually doing nothing but sleeping and resting because of the flu, here I am back to blogging! :)

A very neat feature of SQL Server is the way it lets the DBA to control connections. To this end, you can explicitly control connections with specific commands and methods. Even though this is not usually needed, there are some cases where you might need to make use of this feature. One such a case is when you are presented with the error:

"Exclusive access could not be obtained because the database is in use"

In SQL Server you might be presented with the above error message in the case you are trying to perform an operation on a database that requires exclusive access. One such operation is the RESTORE.

Exclusive access is when an operation needs to run on a database (or another SQL Server object) without any other operations/sessions accessing the same database/object at the same time.

So, what can you do when you get this error message? You still need to perform the RESTORE right? :)

Here's a common way of doing that:

1. Set the database in SINGLE_USER mode and forcibly terminate other connections:

USE [master]
ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

The above command will set the database in SINGLE_USER MODE and additionally the ROLLBABK IMMEDIATE termination option will roll back all the incomplete transactions. It will also disconnect any other connections to the database.

*Note: After issuing the above command, you will only be able to use a single connection to the database. So, if you are already using a query window connected to the target database, either restore the database by using the RESTORE T-SQL command or close the query window and restore the target database by using the Restore Database Wizard from SQL Server Management Studio.

2. Restore the database.

3. Set the database back to MULTI_USER mode:

USE [master]
ALTER DATABASE [DATABASE_NAME]
SET MULTI_USER;

That's it! You have successfully restored the database!

And here's an alternative way (a little bit more complex):

1. If there are any SQL Server logins granted any access on the target database then disable them:

alter login [LOGIN_NAME] disable

*Note: Even though now you have disabled the login(s) there still might exist active connections to the database.

2. To find and terminate existing connections on the target database perform the following:
-- Query that returns all the SPIDs (process IDs)/sessions established to a given database:
SELECT spid,loginame,login_time,program_name
FROM [master]..sysprocesses
WHERE DBID=DB_ID('DATABASE_NAME')

By collecting the above SPIDs you get the sessions that must be forcibly terminated (be careful not to forcibly terminate your own session! :) By including the columns loginame, login_time and program_name in the above query, you are able to identify and exclude the spid that belongs to your session (that is, the DBA session).

You can terminate a session by executing the T-SQL Statement:

KILL @SPID

*Note 1: Replace @SPID with the appropriate SPID number.
*Note 2: You can even create a user-defined function undertaking this task.

3. Set the database in SINGLE_USER mode:

USE [master]
ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER;*Note: After issuing the above command, you will only be able to use a single connection to the database. So, if you are already using a query window connected to the target database, either
restore the database by using the RESTORE T-SQL command or close the query window and restore the target database by using the Restore Database Wizard from SQL Server Management Studio.

4. Restore the database.
5. Set the database back to MULTI_USER mode:

USE [master]
ALTER DATABASE [DATABASE_NAME]
SET MULTI_USER;


Remarks and Considerations
------------------------------
In the case of a RESTORE operation I would personally prefer the first method as it is simpler. With a single T-SQL Statement you set the database in SINGLE_USER MODE and also terminate all the active connections immediately and roll back all the incomplete transactions.

The second method is more preferable in cases where more "elegant" session control is required. This method allows the DBA to terminate sessions one-by-one explicitly instead of the first method which massively terminates all connections.

Choosing one of the two ways I guess that relies to the urgency of the RESTORE operation, the judgment of the DBA and also any Business Policies that might stand for such cases.

Because both the abovementioned methodologies have to do with forcibly terminating connections to a database in SQL Server, you must be extremely careful when using them as you might accidentally cause severe data loss when applying them inappropriately and without the necessary authorization from the Management.