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

by

Troubleshooting Guide: Error 5060 – Nonqualified Transactions Rollback in SQL Server 2025

Why This Error Happens

Error 5060 occurs when SQL Server 2025 initiates a database shutdown, restore, or offline operation while active transactions are still running against the database. These “nonqualified transactions” (transactions that haven’t been committed or explicitly bound to the operation) must be rolled back before SQL Server can safely complete the requested state change. This typically happens due to long-running queries, orphaned sessions, blocking chains, or an abrupt attempt to take a database offline/restore it without first draining active connections.

Step-by-Step Resolution Guide

Step 1: Identify Active Sessions and Transactions

  • Open SQL Server Management Studio (SSMS) and connect to the instance.
  • Run the following query to find active transactions on the affected database:
  SELECT session_id, status, command, blocking_session_id, wait_type, wait_time
  FROM sys.dm_exec_requests
  WHERE database_id = DB_ID('YourDatabaseName');
  • Note any long-running or blocking session IDs for further action.

Step 2: Allow the Rollback to Complete Naturally

  • Monitor rollback progress using:
  SELECT percent_complete, estimated_completion_time
  FROM sys.dm_exec_requests
  WHERE command = 'KILLED/ROLLBACK';
  • Avoid forcibly stopping the SQL Server service (via services.msc or net stop MSSQLSERVER) mid-rollback, as this can lead to database corruption requiring a restore from backup.
  • Large transactions may take significant time to roll back proportional to the size of the original operation.

Step 3: Terminate Blocking Sessions (If Rollback Stalls)

  • If the rollback appears stuck (0% progress for an extended period), identify blocking sessions using sp_who2 or the sys.dm_tran_locks DMV.
  • Carefully terminate only confirmed orphaned sessions:
  KILL <session_id>;
  • Check Windows Server 2025 Event Viewer (Applications and Services Logs > Microsoft > Windows > SQLServer) for correlating I/O or disk subsystem errors that may be slowing the rollback.

Step 4: Adjust Application Connection Handling

  • Review the application’s connection pooling and transaction management logic to ensure explicit COMMIT or ROLLBACK statements are issued before attempting administrative operations (e.g., ALTER DATABASE SET OFFLINE).
  • Implement a connection drain/grace period in deployment scripts before executing database state changes, typically 30–60 seconds.
  • Update connection strings to include appropriate Connection Timeout and Command Timeout values to prevent indefinite transaction hangs.

Alternative or Verification Steps

  • Verify the fix by re-running the database state change (e.g., ALTER DATABASE ... SET OFFLINE) during a low-traffic window and confirming no Error 5060 messages appear in the SQL Server Error Log (Management > SQL Server Logs in SSMS).
  • If rollbacks consistently stall or fail, restart the SQL Server service during a maintenance window and verify database consistency afterward using DBCC CHECKDB('YourDatabaseName').