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

by

Troubleshooting Guide: Error 5059 – Database in Transition

Why This Error Happens

This error occurs when SQL Server receives an ALTER DATABASE command while the target database is already in the middle of a state transition, such as completing a previous ALTER DATABASE operation, finishing a database mirroring failover, or processing an AUTO_CLOSE event. SQL Server locks the database configuration briefly during these transitions to maintain metadata consistency, rejecting any concurrent modification requests. This is frequently triggered by automated deployment scripts or monitoring tools that issue rapid, overlapping ALTER DATABASE statements without waiting for the prior command to fully commit.

Step-by-Step Resolution Guide

Step 1: Identify Concurrent or Overlapping Processes

  • Open SQL Server Management Studio (SSMS) and run sp_who2 or query sys.dm_exec_requests to check for other sessions executing ALTER DATABASE against the same database.
  • Check Windows Server 2025 Task Scheduler and any CI/CD pipelines (e.g., Azure DevOps agents) for scripts that might be firing simultaneous schema or state change commands.
  • Look at the SQL Server Error Log (via SSMS > Management > SQL Server Logs) for the exact timestamp of the 5059 error to correlate with other transactional activity.

Step 2: Wait for the Current Transition to Complete

  • Run the following query to check the current state of the database transition:
SELECT name, state_desc, user_access_desc 
FROM sys.databases 
WHERE name = 'YourDatabaseName';
  • If state_desc shows anything other than ONLINE (e.g., RESTORING or ONLINE_IN_PROGRESS), pause your script execution for 10-15 seconds before retrying.
  • For mirroring or Always On Availability Group setups, verify failover completion status using sys.dm_hadr_database_replica_states.

Step 3: Implement a Retry Logic with Delay

  • Modify your deployment or automation scripts to include a WAITFOR DELAY or a TRY...CATCH block with a retry counter to handle transient locking.
  • Example T-SQL pattern:
BEGIN TRY
    ALTER DATABASE [YourDatabaseName] SET MULTI_USER;
END TRY
BEGIN CATCH
    WAITFOR DELAY '00:00:05';
    ALTER DATABASE [YourDatabaseName] SET MULTI_USER;
END CATCH
  • Ensure no other active connections are forcing a SINGLE_USER rollback simultaneously by checking sys.dm_exec_sessions.

Step 4: Restart the SQL Server Service (If Stuck)

  • If the transition state appears “stuck” (lasting longer than 5 minutes), open Services.msc on Windows Server 2025.
  • Locate SQL Server (MSSQLSERVER) or the specific named instance, right-click, and select Restart.
  • Note: This will terminate all active connections, so ensure this is done during a maintenance window.

Alternative or Verification Steps

  • Verify the Fix: Re-run the ALTER DATABASE statement and immediately check sys.databases to confirm state_desc returns to ONLINE without further Error 5059 occurrences.
  • Alternative Approach: If the issue persists after a restart, use DBCC CHECKDB to rule out underlying corruption that might be preventing the database from exiting the “pending” state.