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

by

Troubleshooting Guide: SQL Server Error 8953 — “Repair: Deleted off-row data column with ID %I64d”

Why This Error Happens

Error 8953 occurs when DBCC CHECKDB detects and repairs corruption in off-row data pages (typically LOB or overflow data referenced by a column that stores large values outside the main data row). This is usually the result of underlying storage-level corruption caused by faulty disk hardware, improper shutdowns, storage driver issues, or a corrupted database page that broke the pointer chain between the in-row data and its off-row allocation unit. Because this message appears during a repair operation, it indicates that data has already been permanently removed to restore structural consistency, meaning some information loss has likely occurred.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of Corruption

  • Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; in SQL Server Management Studio (SSMS) to get the full list of affected objects.
  • Review the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG to cross-reference the timestamp of Error 8953 with any I/O or disk warnings.
  • Identify the specific table and index ID referenced in the repair message using DBCC PAGE or sys.objects/sys.partitions queries.

Step 2: Restore from a Clean Backup (Preferred Method)

  • Locate the most recent verified full backup taken before the corruption event, ideally stored on a separate volume from the production data files.
  • Use RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabaseName.bak' WITH REPLACE, RECOVERY; to restore to a clean state.
  • Apply any subsequent transaction log backups to minimize data loss, using WITH NORECOVERY for intermediate logs and WITH RECOVERY on the final one.
  • Validate hardware health in Windows Server 2025 via Server Manager > Storage > Disks or by running chkdsk D: /f /r on the affected volume to rule out recurring physical issues.

Step 3: Use DBCC CHECKDB Repair (If No Backup Is Available)

  • Set the database to single-user mode first: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Run DBCC CHECKDB (YourDatabaseName, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS; understanding this will permanently remove corrupted off-row data pages.
  • Return the database to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;
  • Document every deleted column ID reported in the output for downstream data-reconciliation purposes.

Step 4: Investigate and Eliminate the Root Cause

  • Check Windows Event Viewer (Windows Logs > System) for Event ID 51 or 153, which indicate underlying disk or storage controller problems.
  • Update storage drivers and firmware through Windows Update or the hardware vendor’s utility to prevent recurrence.
  • Enable CHECKSUM page verification on the database (ALTER DATABASE YourDatabaseName SET PAGE_VERIFY CHECKSUM;) if it isn’t already active, to catch future corruption earlier.

Alternative or Verification Steps

  • After remediation, re-run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; and confirm it returns zero allocation or consistency errors.
  • If corruption recurs shortly after repair, treat it as a hardware-level issue and engage your storage/SAN vendor for diagnostics rather than repeating repair operations.