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

by

Troubleshooting Guide: SQL Server Error 5084

Why This Error Happens

Error 5084 occurs when SQL Server 2025 attempts to change a database option (such as AUTO_CLOSE, READ_ONLY, SINGLE_USER, or COMPATIBILITY_LEVEL) but fails to complete the operation. This is typically caused by the database being in an inconsistent state, active connections preventing an exclusive lock required for the ALTER DATABASE statement, or insufficient permissions on the service account. Corrupted system catalog metadata or a pending database recovery process can also block the option change from committing successfully.

Step-by-Step Resolution Guide

Step 1: Verify Active Connections and Session Locks

  • Open SQL Server Management Studio (SSMS) and run SELECT * FROM sys.dm_exec_sessions WHERE database_id = DB_ID('YourDatabaseName'); to identify blocking sessions.
  • If connections exist, set the database to single-user mode first using ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Terminate orphaned sessions manually using KILL [session_id] if rollback does not clear them.

Step 2: Check and Repair Service Account Permissions

  • Navigate to SQL Server Configuration Manager on Windows Server 2025 / Windows 11.
  • Confirm the SQL Server service account has db_owner or sysadmin rights on the target database.
  • Validate NTFS permissions on the database files located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\, ensuring the service account has Full Control.

Step 3: Run Database Consistency Checks

  • Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to detect metadata or page-level corruption.
  • If errors are returned, run DBCC CHECKDB('YourDatabaseName', REPAIR_REBUILD); in single-user mode.
  • Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell after repairs complete.

Step 4: Reapply the Database Option Change

  • Reconnect via SSMS and re-run the original ALTER DATABASE statement that triggered Error 5084.
  • Monitor the SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for confirmation messages or recurring failures.
  • Set the database back to multi-user mode using ALTER DATABASE [YourDatabaseName] SET MULTI_USER;

Alternative or Verification Steps

  • Confirm the fix by querying SELECT name, is_read_only, is_auto_close_on FROM sys.databases WHERE name = 'YourDatabaseName'; to verify the option was successfully applied.
  • If the issue persists, restore the database from the most recent clean backup using RESTORE DATABASE [YourDatabaseName] FROM DISK = 'BackupPath.bak' WITH REPLACE; and reapply configuration changes.