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

by

Why This Error Happens

Error 5004 occurs when SQL Server cannot acquire the exclusive lock required to modify database-level properties (such as file size, collation, or recovery model) because active user connections or system processes are still bound to the database. This typically happens when the database is being accessed by another session, a replication/mirroring process, or when SQL Server Management Studio itself holds an open connection in the target database context. Until all sessions release their locks, ALTER DATABASE commands requiring single-user or exclusive access will fail.

Step-by-Step Resolution Guide

Step 1: Identify Active Connections Blocking the Database

  • Open a new query window connected to the master database (not the target database).
  • Run SELECT session_id, status, host_name, program_name FROM sys.dm_exec_sessions WHERE database_id = DB_ID('YourDatabaseName'); to list all active sessions.
  • Note any session_id values other than your own; these must be terminated before proceeding.

Step 2: Switch the Database to SINGLE_USER Mode

  • From the master context, run:
    ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • This forcibly disconnects all other sessions and rolls back any open transactions, clearing the lock conflict.
  • If this command also fails with Error 5004, confirm you are not currently using the target database in your active query window — switch to master first.

Step 3: Execute the Original ALTER DATABASE Command

  • Once in SINGLE_USER mode, re-run the intended ALTER DATABASE statement (e.g., changing file growth, collation, or recovery model).
  • Example: ALTER DATABASE YourDatabaseName MODIFY FILE (NAME = 'YourDataFile', SIZE = 500MB);

Step 4: Revert the Database to MULTI_USER Mode

  • Run ALTER DATABASE YourDatabaseName SET MULTI_USER; to restore normal access.
  • Confirm no application connection strings were left pointing to a stale session during the SINGLE_USER window.

Step 5: Check for Service-Level Locks (If Issue Persists)

  • Open Services.msc on Windows Server 2025 / Windows 11 and verify no SQL Server Agent jobs or SSIS packages are actively querying the database.
  • Restart the SQL Server (MSSQLSERVER) service only as a last resort, since this disconnects all users system-wide.

Alternative or Verification Steps

  • Verify the fix by running SELECT DATABASEPROPERTYEX('YourDatabaseName', 'UserAccess'); — it should return MULTI_USER after Step 4 completes successfully.
  • If SINGLE_USER mode still fails to apply, check SQL Server Configuration Manager for lingering linked server connections or replication subscribers still bound to the database, and disable them temporarily before retrying.