SQL Error 3101 is thrown by #SQL when a #database is interrupted when it is being restored.
More details about the error message;
SQL SERVER – Msg 3101, Level 16 – Exclusive Access Could not be Obtained Because the Database is in Use
For fix this #error, try the following tips;
- 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')
- 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'
- If you want, you can take the database offline, which would drop all the connection. Then you can perform restore.
- 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.
- 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.
For more info >>
https://ibmimedia.com/blog/74/solve-sql-error-3101
