Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

SQL Server restore error 3101: exclusive access could not be obtained because the database is in use

Technical article
Backup & Recovery

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

Error 3101, "Exclusive access could not be obtained because the database is in use", means at least one other session still has the target database open, so the restore cannot take the exclusive access it needs. A typical culprit is an application, report, job or open query window connected to the database you are overwriting. The safe fix is to identify those sessions, take the database to single-user mode in a controlled way, restore, and put it back to multi-user.

What the error means

Microsoft lists error 3101 in its Database Engine error reference for the 3000 range as severity 16 with the message "Exclusive access could not be obtained because the database is in use." The SSMS restore page (page metadata date 2023-12-08) shows the same message, as raised through SQL Server Management Objects (SMO), when you restore over an existing database and do not tick Close existing connections to destination database. It says: "Restore operations may fail if there are active connections to the database."

The documentation for the RESTORE statement (page metadata date 2023-05-18) says something that looks like the opposite: "During an offline restore, if the specified database is in use, RESTORE forces the users off after a short delay." Both statements are on Microsoft's pages, and Microsoft does not explain how they fit together. In practice the SSMS page shows the restore failing with 3101, so treat it as a real condition and prove which sessions are involved on your own instance before you act. See "Risks and limits".

Two terms matter for the fix, both from the ALTER DATABASE SET options page (page metadata date 2026-06-29):

  • SINGLE_USER allows one user at a time. If other users are connected, the ALTER DATABASE statement is blocked until they disconnect, unless a termination clause overrides this. The database stays in single-user mode even if the user who set it signs out, and then "a different user, but only one, can connect".
  • RESTRICTED_USER allows only members of db_owner, dbcreator and sysadmin, with no limit on their number.

The termination clause decides when incomplete transactions are rolled back: ROLLBACK AFTER n SECONDS, ROLLBACK IMMEDIATE, or NO_WAIT. If the clause is omitted, "the ALTER DATABASE statement waits indefinitely if there's any lock on the database."

Likely causes

Cause What you see How to confirm
Applications or services still connected Sessions with the database as current database, with a recent last_request_end_time Query sys.dm_exec_sessions joined to sys.databases (below) and read program_name and host_name
Your own query window is using the database One of the sessions is your own, or a colleague's, in SSMS Check program_name and login_name against your own connection
Open transaction held by an idle session Session status sleeping with open_transaction_count above 0 The same query; the column is documented on the sessions page
A long-running request in the database Session status running sys.dm_exec_requests for that session_id
Database left in single-user mode by an earlier step user_access_desc is SINGLE_USER in sys.databases, and one session holds the slot sys.databases, then identify the one session
A restore already running or half-finished against the same name A restore request in sys.dm_exec_requests command and percent_complete (documented for RESTORE DATABASE)

Prove the cause before changing anything

These queries use column names documented in the Microsoft pages cited, and none has been run or lab-tested. They are read-only.

  1. See who is using the database. The database_id column of sys.dm_exec_sessions (page metadata date 2026-11-02) is the "ID of the current database for each session". On SQL Server 2019 and earlier you need VIEW SERVER STATE to see all sessions, and on 2022 and later VIEW SERVER PERFORMANCE STATE:
SELECT s.session_id, s.login_name, s.host_name, s.program_name,
       s.status, s.open_transaction_count, s.last_request_end_time
FROM sys.dm_exec_sessions AS s
JOIN sys.databases AS d ON d.database_id = s.database_id
WHERE d.name = N'Sales'
  AND s.session_id <> @@SPID;
  1. Check the access mode of the database. sys.databases (page metadata date 2026-04-27) returns user_access_desc (MULTI_USER, SINGLE_USER or RESTRICTED_USER) and state_desc:
SELECT name, state_desc, user_access_desc
FROM sys.databases
WHERE name = N'Sales';
  1. For a session that is running, look at what it is doing in sys.dm_exec_requests (columns session_id, command, blocking_session_id, percent_complete).

One limit: database_id is only the session's current database. A session whose current database is master but which is querying Sales through a three-part name will not appear in query 1. If query 1 returns nothing and the error persists, the lock-level views are the next place to look.

Fix

Choose the least destructive option that fits. Before any option that disconnects people, tell the application owner. Rolled-back transactions are lost.

Option 1: SSMS. On the Options page of the Restore dialog, tick Close existing connections to destination database. Per the SSMS page, this "sets the database to single user mode before the restore operations, and sets the database to multi-user mode when complete". The same page shows 3101 as the result of not ticking it.

Option 2: T-SQL, single-user with rollback. Take a tail-log backup first if the database is online, uses the full or bulk-logged recovery model, and you need the work since the last log backup. The RESTORE page says restoring without it "results in an error" unless REPLACE or STOPAT is used. The Microsoft example for the access change is:

USE master;
GO
ALTER DATABASE [Sales]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
-- run the RESTORE here, from the same session if you can

ROLLBACK IMMEDIATE disconnects the other sessions and rolls back their open transactions, so their uncommitted work is discarded. ROLLBACK AFTER 30 SECONDS gives short transactions time to finish. Because the database stays single-user after you sign out, and "a different user, but only one" can then connect, run the restore from the same session straight away. Applications that reconnect automatically can take the single slot, so a practical step is to pause them first.

After the restore, set the access mode back and check it:

ALTER DATABASE [Sales] SET MULTI_USER;

Then run the sys.databases query above and confirm user_access_desc is MULTI_USER. A database left in SINGLE_USER blocks every other user, so do not skip this step.

Option 3: end specific sessions. The KILL page (page metadata date 2025-07-09) says KILL requires ALTER ANY CONNECTION (included in sysadmin and processadmin), you cannot kill your own session, and a session with much to undo can take a long time. Use KILL <session_id> WITH STATUSONLY to read rollback progress. Microsoft warns that re-running KILL risks hitting a new process that reuses the session ID. Never kill system processes.

Do not add REPLACE to get past 3101. It is a different control. Per the RESTORE arguments page (page metadata date 2022-05-10), REPLACE deletes the existing database and overrides the safety check against overwriting a different database and the requirement to back up the tail of the log, so committed work can be lost. REPLACE does not release other sessions.

Example (illustrative). On SQL01, a restore of Sales from \\FS01\SQLBackups\Sales_full.bak fails with 3101. The sessions query returns session 61 (login_name CONTOSO\svc-report, host_name APP01, program_name ReportingApp, status sleeping) and session 74, an SSMS query window belonging to the administrator's colleague. After the application owner pauses the reporting service, the administrator takes a tail-log backup, sets SINGLE_USER WITH ROLLBACK IMMEDIATE, restores, sets MULTI_USER, and re-enables the service.

Confirm the fix

  • sys.databases shows state_desc ONLINE and user_access_desc MULTI_USER.
  • The restore is recorded in msdb. The restorehistory table (page metadata date 2017-03-03) holds one row per restore with restore_type (D = database, L = log), recovery (1 = RECOVERY, 0 = NORECOVERY) and replace (1 if REPLACE was used). Rows exist only on the instance that ran the restore and are pruned by sp_delete_backuphistory.
  • The application owner runs a real transaction and confirms the data matches the restore point. A restore that completes does not prove the right data came back.

What to watch afterwards

  • Schedule restores for a window in which the application is stopped, rather than relying on disconnecting sessions at the last moment.
  • Alert on any database whose user_access_desc is not MULTI_USER outside a maintenance window.
  • If the same sessions reappear every time, find which service reconnects and give it a maintenance state.
  • Update the restore runbook so that the tail-log backup step is explicit.

Risks and limits

  • These queries and statements have not been run or lab-tested. Test them on a non-production instance first.
  • Conflicting guidance: the RESTORE page says an offline restore forces users off after a short delay, while error 3101 and the SSMS page show restores failing for exclusive access. Microsoft does not reconcile the two, so test on your own instance.
  • ROLLBACK IMMEDIATE and KILL discard uncommitted work. A restore over an existing database discards everything after the restore point. A tail-log backup is the protection.
  • Always On, log shipping and mirroring databases have additional rules not covered here.

Sources