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

by

Troubleshooting Guide: SQL Server Error 5202 — DBCC SHRINKDATABASE Waiting on Snapshot Transaction

Why This Error Happens

This error occurs because DBCC SHRINKDATABASE cannot reclaim space until all open snapshot isolation transactions referencing older data versions have completed. Long-running transactions, orphaned sessions, or an uncommitted transaction holding a low timestamp in the version store will block the shrink operation indefinitely. This is common in databases with READCOMMITTEDSNAPSHOT or ALLOWSNAPSHOTISOLATION enabled, where the version store cannot be truncated while active references exist.

Step-by-Step Resolution Guide

Step 1: Identify the Blocking Snapshot Transactions

  • Run the following query to find active snapshot transactions holding up version cleanup:
SELECT session_id, transaction_id, elapsed_time_seconds, is_user_transaction
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;
  • Note the session_id values returned; these are the sessions preventing the shrink from completing.

Step 2: Investigate and Terminate Long-Running Sessions

  • Cross-reference session IDs with sys.dm_exec_sessions and sys.dm_exec_requests to check for idle or orphaned connections.
  • Use KILL <session_id> only after confirming the session is not performing critical work.
  • Check application connection pooling settings, as improperly closed connections often leave snapshot transactions open.

Step 3: Check for Open Transactions in System Databases

  • Query sys.dm_tran_database_transactions to confirm no open transactions exist in tempdb or the target database.
  • Restart the SQL Server Agent job or application service that may be holding a transaction open past its expected lifecycle.
  • On Windows Server 2025, verify via Services.msc that no stale SQLAgent$INSTANCENAME processes are holding locks.

Step 4: Retry DBCC SHRINKDATABASE During Low-Activity Window

  • Schedule the shrink operation during a maintenance window with minimal concurrent read/write activity.
  • Execute:
DBCC SHRINKDATABASE (YourDatabaseName, 10);
  • Monitor sys.dm_exec_requests for wait type DBCC to confirm progress rather than continued blocking.

Step 5: Adjust Snapshot Isolation Settings if Persistent

  • If the issue recurs frequently, consider temporarily disabling ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT before maintenance windows:
ALTER DATABASE YourDatabaseName SET ALLOW_SNAPSHOT_ISOLATION OFF;
  • Re-enable the setting immediately after the shrink operation completes successfully.

Alternative or Verification Steps

  • Verify the fix by re-running sys.dm_tran_active_snapshot_database_transactions — an empty result set confirms no blocking transactions remain before retrying the shrink.
  • If the error persists despite no visible blocking transactions, restart the SQL Server service during a maintenance window to forcibly clear the version store, then re-attempt DBCC SHRINKDATABASE.