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.

SQL SERVER - Msg 3101, Level 16 - Exclusive Access Could not be Obtained Because the Database is in Use restore-in-use-01-800x324

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')
  1. 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'
  1. If you want, you can take the database offline, which would drop all the connection. Then you can perform restore.
  2. 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.
  3. 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)

Quest

SQL Backup, SQL Restore, SQL Server
Previous Post
SQL SERVER – System. Security. Cryptography. CryptographicException – There Was an Error Generating the XML Document
Next Post
SQL SERVER – Msg 3136, Level 16 – This Differential Backup Cannot be Restored Because the Database has not Been Restored to the Correct Earlier State

Related Posts

6 Comments. Leave new

  • Putting database in single user mode with immediate rollback option….

    Reply
    • What is someone else gets into database after you change to single user an run restore command?

      Reply
      • Hey Pinal,

        Idea is to put database in single user mode and start database restore within the same batch – Chances are very less that someone grasp the connection, If some one does, identify the user and ask him to close the connection. In another case, you can forcefully close the connection, it should be okay for non-prod servers.

        Thank you for all your awesome blogs !!

      • Thanks Anil.

  • yea. that exactly how i run the script. all on the same batch. and it works fine.

    Reply

Leave a Reply