SQL Server Error 3101

Error number

3101

Severity

16 (user-correctable error)

State

1

Product

Microsoft SQL Server Database Engine

Versions

All supported SQL Server versions (2016 to 2025), Azure SQL Managed Instance

Category

Backup & Restore

Last reviewed

23 October 2026

What it means

Error 3101 means RESTORE needs the database to itself but at least one other connection (another query window, an application, a job) was still using it.

Restoring over an existing database replaces every page in it, so SQL Server must hold an exclusive lock on the whole database. Any session whose current database is the target holds a shared database lock, and while one exists the RESTORE cannot start. It waits briefly for those sessions to leave (about 20 seconds in our test on SQL Server 2025, shown as an LCK_M_X wait on the database), then raises 3101 and ends with Msg 3013 ... is terminating abnormally. The existing database is not changed.

The message does not say who is connected, so the first step is always to list the sessions. A frequent surprise is that the blocker is another of your own connections: a second query window in SSMS whose database drop-down shows the target, or Object Explorer. (If the RESTORE itself runs in the target database, you get error 3102 instead, which tells you to use master.) Application pools, SQL Server Agent jobs and reporting tools that reconnect automatically are the other usual suspects.

Common causes

• Another query window or Object Explorer in your own SSMS is connected to the database (the database chosen in its drop-down).
• Applications, web servers or connection pools keep connections open to the database.
• A colleague, a report or a monitoring tool is connected to the database.
• A SQL Server Agent job or a replication or ETL process connects on a schedule and reconnects as soon as it is disconnected.

Diagnose it

Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.

SELECT s.session_id, s.login_name, s.host_name, s.program_name,
       s.status, s.last_request_end_time,
       CASE WHEN s.session_id = @@SPID THEN 'this session' ELSE '' END AS note
FROM sys.dm_exec_sessions AS s
WHERE s.database_id = DB_ID(N'AppDb');

SELECT request_session_id, resource_type, request_mode, request_status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID(N'AppDb')
  AND request_session_id <> @@SPID;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

USE master;
RESTORE DATABASE AppDb
FROM DISK = N'D:\Backups\AppDb.bak'
WITH REPLACE;   -- only if you mean to overwrite AppDb (see the caution on error 3159)

USE master;
ALTER DATABASE AppDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE AppDb
FROM DISK = N'D:\Backups\AppDb.bak'
WITH REPLACE;
-- If the restore failed: ALTER DATABASE AppDb SET MULTI_USER;

Examples

Another connection is using the database

Throwaway database; run as two sessions at the same time. The backup goes to the instance default backup folder. On SQL Server 2025 the RESTORE waited about 20 seconds for session 1 to leave before failing; had session 1 disconnected in that time, the restore would have succeeded.

-- Session 1: an application connection using the database
USE AppDb;
WAITFOR DELAY '00:00:30';

-- Session 2, at the same time: restore over it while session 1 is connected
USE master;
DECLARE @bak nvarchar(260) = CAST(SERVERPROPERTY('InstanceDefaultBackupPath') AS nvarchar(200)) + N'\cws_errlab_3101.bak';
BACKUP DATABASE AppDb TO DISK = @bak WITH INIT;
RESTORE DATABASE AppDb FROM DISK = @bak WITH REPLACE;

Msg 3101, Level 16, State 1, Line 4
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.

Your own session is in the database (error 3102, not 3101)

Throwaway database. When the session running the RESTORE is itself using the target database, SQL Server does not wait: it raises the closely related error 3102 at once. Run the restore from master.

USE AppDb;   -- the query window is connected to the database being restored
DECLARE @bak nvarchar(260) = CAST(SERVERPROPERTY('InstanceDefaultBackupPath') AS nvarchar(200)) + N'\cws_errlab_3101.bak';
BACKUP DATABASE AppDb TO DISK = @bak WITH INIT;
RESTORE DATABASE AppDb FROM DISK = @bak WITH REPLACE;

Msg 3102, Level 16, State 1, Line 4
RESTORE cannot process database 'AppDb' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.

How to prevent it

Script planned restores to start with USE master and a check of connected sessions, and stop dependent applications and Agent jobs first. For automated refreshes of test copies, include the SINGLE_USER step and the restore in one batch so the window for reconnections is closed.

Give applications their own logins, so that login_name and program_name in the session list tell you immediately who to contact. The same in-use problem stops DROP DATABASE (error 3702) and other operations that need exclusive access.

Common questions

Learn more on CodeWithSQL

• Transactions, locks and deadlocks

Official documentation

• RESTORE statements (Transact-SQL)
• Set a database to single-user mode
• sys.dm_exec_sessions (Transact-SQL)

Last technically reviewed 23 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.