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

by

Troubleshooting Guide: Error 5231 – Deadlock During Object Locking Check (DBCC CHECKDB)

Why This Error Happens

Error 5231 occurs when DBCC CHECKDB (or CHECKTABLE/CHECKFILEGROUP) attempts to acquire an internal lock on an object to validate its consistency, but another active session holds a conflicting lock on the same object, resulting in a deadlock. This is commonly triggered by concurrent schema changes, heavy OLTP write activity, or long-running transactions competing for the same resource during the integrity check. Because SQL Server automatically resolves deadlocks by choosing a victim, the consistency check skips the object rather than risk blocking production workloads indefinitely.

Step-by-Step Resolution Guide

Step 1: Identify Blocking Sessions at the Time of the Check

  • Query sys.dm_exec_requests and sys.dm_tran_locks immediately before/during the CHECKDB run to find sessions holding locks on the affected object ID.
  • Cross-reference the Object ID from the error message using: SELECT OBJECT_NAME(object_id) FROM sys.objects WHERE object_id = <ld>;
  • Check SQL Server Error Log and Windows Event Viewer (Applications and Services Logs > Microsoft > SQL Server) for correlating deadlock graphs if Trace Flag 1222 is enabled.

Step 2: Re-run CHECKDB During a Low-Activity Window

  • Schedule the integrity check during off-peak hours using SQL Server Agent Job Scheduler to minimize contention.
  • Use the WITH TABLOCK option to reduce internal snapshot overhead:
    DBCC CHECKDB ('YourDatabaseName') WITH TABLOCK, NO_INFOMSGS, ALL_ERRORMSGS;
  • Note: TABLOCK reduces concurrency but significantly lowers the chance of internal deadlocks by taking a bulk-update style lock instead of relying purely on the internal database snapshot.

Step 3: Isolate the Check Using a Database Snapshot

  • Manually create a database snapshot to run the consistency check against a static, point-in-time copy, bypassing live transactional locks entirely.
  • Run the command: CREATE DATABASE YourDB_Snapshot ON (NAME = YourDB, FILENAME = 'C:\SQLData\YourDB_Snap.ss') AS SNAPSHOT OF YourDB;
  • Execute DBCC CHECKDB against the snapshot name instead of the live production database.

Step 4: Check for Long-Running Transactions and Orphaned Connections

  • Open SQL Server Management Studio (SSMS) and navigate to Activity Monitor to look for sessions with a status of “Suspended” holding locks on system objects.
  • Terminate orphaned or zombie connections using KILL <session_id>; only after confirming they are not critical production processes.
  • Verify that automated ETL jobs or replication agents are not scheduled to run concurrently with the maintenance window.

Alternative or Verification Steps

  • Verification: Re-run DBCC CHECKDB with the WITH TABLOCK syntax immediately after implementing the fix; a clean run will return “CHECKDB found 0 allocation errors and 0 consistency errors” for the specific Object ID.
  • Alternative: If deadlocks persist even with TABLOCK, restore a recent backup to a secondary/staging server and run DBCC CHECKDB there to validate the integrity of the data outside the production environment entirely.