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

by

Troubleshooting Guide: SQL Server Error 931 – Database Fragment Reference Failure

Why This Error Happens

Error 931 occurs when SQL Server’s internal storage engine attempts to access a database fragment (a logical file allocation unit) that is missing from the database’s metadata or filegroup mapping. This is typically caused by database corruption, an incomplete or interrupted file growth/shrink operation, or a mismatch between the database’s system catalog and the actual physical file structure on disk. In rare cases, this can also result from improperly restoring a database backup that was taken mid-transaction or from a storage-level failure (SAN/disk corruption) that affected one or more secondary data files.

Step-by-Step Resolution Guide

Step 1: Verify Database and File Integrity

  • Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify the scope of corruption.
  • Check SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG) for related 5172 or 823/824 I/O errors occurring around the same timestamp.
  • Confirm all .mdf, .ndf, and .ldf files listed in sys.master_files physically exist in their expected directories.

Step 2: Take the Database Offline and Attempt Repair

  • Set the database to single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Run DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS); only if a recent backup is unavailable, as this may discard corrupted fragment data.
  • Switch back to multi-user mode once repair completes: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 3: Restore From a Known-Good Backup

  • Navigate to your backup directory (commonly C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\) and locate the last verified full backup.
  • Execute RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\Path\To\Backup.bak' WITH REPLACE, RECOVERY;
  • Apply any subsequent transaction log backups in sequence using WITH NORECOVERY until the final log file, which should use WITH RECOVERY.

Step 4: Check Underlying Storage Health

  • Open Event Viewer on Windows Server 2025/Windows 11 and inspect the System log for Disk or Ntfs warnings (Event ID 7, 51, or 153) coinciding with the SQL error.
  • Run chkdsk C: /f /r (or the appropriate drive letter hosting your SQL data files) during a scheduled maintenance window to rule out physical disk-level corruption.

Alternative or Verification Steps

  • After remediation, re-run DBCC CHECKDB with no repair options to confirm zero allocation or consistency errors are reported.
  • If the error persists post-restore, consider migrating the database to a new filegroup/file structure via CREATE DATABASE ... FOR ATTACH with freshly exported data, bypassing the corrupted fragment metadata entirely.