SQL SERVER – Msg 3101, Level 16 – Exclusive Access Could not be Obtained Because the Database is in Use

This error might have seen by DBAs and they know how to fix it. This blog goes in little details about what are the various options available. Here is the simple reproduction of the error message related to exclusive access.

USE master
GO
CREATE DATABASE SQLAuthority
GO
BACKUP DATABASE SQLAuthority TO DISK = 'SQLAuth.bak'
GO
-- at this point open another query window and connect to SQLAuthority
RESTORE DATABASE SQLAuthority FROM DISK = 'SQLAuth.bak'
GO

Here are the few things which we can check.

1. Use sys.dm_exec_sessions to identify who is using the database which we are restoring.

SELECT session_id
	,host_name
	,program_name
	,client_interface_name
	,login_name
	,STATUS
	,is_user_process
FROM sys.dm_exec_sessions
WHERE database_id = db_id('SQLAuthority')

2. The database that we are trying to restore should not be set as the default database for the login that we are using. We can verify the default database for a login by running the query-

SELECT loginname
	,dbname
FROM sys.syslogins
WHERE dbname = 'SQLAuthority'

3. If you want, you can take the database offline, which would drop all the connection. Then you can perform restore.
4. We can also detach the database. By detaching, the data and transaction log files of the database will be available on the same location. If we have to restore the same database at same files when we need to delete the files.
5. Another option would be to drop the database. Once dropped, its files and data are deleted from the disk on the server. When a database is dropped, it is permanently removed and cannot be retrieved without using a previous backup.

Which is the most common option you use to refresh development/QA server from production server?

Reference: Pinal Dave (http://blog.SQLAuthority.com)