How to fix Error 5070 on Windows Server 2025 / Windows 11

by

Why This Error Happens

SQL Server Error 5070 occurs when an administrator attempts to change a database’s state (such as setting it to SINGLE_USER, OFFLINE, or restoring it) while active connections or open transactions still exist against that database. The ALTER DATABASE command requires exclusive access to apply state changes, and SQL Server will not forcibly terminate sessions unless explicitly instructed to do so. This is commonly triggered by lingering application connection pools, open SSMS query windows, or background services (like SQL Server Agent jobs or reporting services) that maintain a live session to the target database.

Step-by-Step Resolution Guide

Step 1: Identify Active Connections to the Database

  • Open a new query window in SQL Server Management Studio (SSMS) connected to the master database.
  • Run the following query to view current sessions:
SELECT session_id, login_name, host_name, program_name, status 
FROM sys.dm_exec_sessions 
WHERE database_id = DB_ID('YourDatabaseName');
  • Note any session_id values other than your own; these are blocking the state change.

Step 2: Terminate Blocking Sessions Manually

  • For each listed session_id, run a KILL command to forcibly disconnect the session:
KILL <session_id>;
  • Confirm in Task Manager or Services (services.msc) that no local application (IIS, SSRS, or a custom Windows Service) is configured to auto-reconnect immediately after being killed.

Step 3: Force Single-User Mode with Rollback

  • Instead of manually killing each session, use the WITH ROLLBACK IMMEDIATE clause to let SQL Server handle disconnection and transaction rollback automatically:
ALTER DATABASE [YourDatabaseName] 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE;
  • This command will terminate all open transactions and connections instantly, allowing the state change to proceed without needing to find each session manually.

Step 4: Apply Your Original State Change

  • Once the database is in SINGLE_USER mode, execute the restore, detach, or configuration change you originally intended.
  • Example for taking the database offline:
ALTER DATABASE [YourDatabaseName] SET OFFLINE;
  • Revert to MULTI_USER mode once the maintenance task is complete:
ALTER DATABASE [YourDatabaseName] SET MULTI_USER;

Alternative or Verification Steps

  • Verify the fix by re-running the sys.dm_exec_sessions query from Step 1; if the result set is empty (aside from your own connection), the database is fully accessible for state changes.
  • If the error persists even after using ROLLBACK IMMEDIATE, check for an orphaned connection from a linked server or a stuck Windows Service in services.msc—restarting the specific service (e.g., SQL Server Agent) often releases a connection handle that SSMS cannot otherwise detect.