Troubleshooting Guide: SQL Server Error 5285 – Missing Columnstore Dictionary

Why This Error Happens

Error 5285 occurs when the dictionary structures backing a nonclustered columnstore index become disconnected from their associated rowgroup, typically due to physical database corruption or an incomplete write operation during index compression. This can result from unexpected server shutdowns, storage subsystem failures, or disk-level corruption that occurs while SQL Server is encoding or compressing column segments. In rare cases, it may also surface after a failed or interrupted index rebuild operation that did not fully commit the dictionary metadata.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of Corruption

• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; to validate whether the corruption is isolated to the columnstore index or affects other structures.
• Review the SQL Server error log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for related 5285 entries and timestamps to identify the affected table and rowgroup.
• Note the exact table name and column ID referenced in the error message for targeted remediation.

Step 2: Drop and Recreate the Nonclustered Columnstore Index

• Identify the index definition first using SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('YourTableName');
• Drop the affected index with DROP INDEX [IndexName] ON [YourTableName];
• Recreate it using CREATE NONCLUSTERED COLUMNSTORE INDEX [IndexName] ON [YourTableName] (Column1, Column2, ...);
• Ensure sufficient tempdb space is available, as columnstore rebuilds are resource-intensive.

Step 3: Validate Underlying Storage Health

• Use Windows Server 2025’s built-in chkdsk C: /f /r (run from an elevated Command Prompt) to rule out disk-level corruption contributing to the issue.
• Check the Windows Event Viewer under Windows Logs > System for Disk or NTFS warnings around the time of the SQL Server error.
• If using virtualized or SAN storage, confirm with your storage administrator that no I/O errors or latency spikes occurred.

Step 4: Restore from Backup if Rebuild Fails

• If dropping and recreating the index does not resolve the issue or DBCC CHECKDB reports additional corruption, restore the affected database from the most recent clean backup.
• Use RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabaseName.bak' WITH REPLACE; and reapply transaction logs if needed.

Alternative or Verification Steps

• After recreating the index, run DBCC CHECKDB again and query sys.column_store_dictionaries to confirm dictionary entries exist for all rowgroups without errors.
• If the error recurs immediately after rebuild, consider rebuilding the entire table’s clustered index first, then reapply the nonclustered columnstore index to rule out underlying heap/page-level corruption.

Troubleshooting Guide: Error 5286 – Page Could Not Be Moved (Active Online Index Build with LOBs)

Why This Error Happens

This error occurs when SQL Server’s shrink or defragmentation process attempts to relocate a data page that is currently locked by an active online index build operation involving LOB (Large Object) data types such as VARCHAR(MAX), VARBINARY(MAX), or XML. Because online index operations maintain a separate mapping structure for LOB pages during the build, the page relocation engine cannot safely move these pages without risking data corruption, so SQL Server blocks the move and raises Error 5286. This is typically a transient concurrency conflict rather than actual corruption, often triggered when DBCC SHRINKFILE, DBCC SHRINKDATABASE, or auto-shrink runs concurrently with a long-running ONLINE = ON index rebuild.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Online Index Operation

• Query sys.dm_exec_requests and sys.index_resumable_operations to find active online index builds: SELECT * FROM sys.dm_exec_requests WHERE command LIKE '%INDEX%';
• Cross-reference the session ID (SPID) with sys.dm_exec_sql_text(sql_handle) to confirm it is a LOB-related index rebuild.
• Note the databaseid and objectid to correlate with the shrink operation that triggered Error 5286.

Step 2: Pause or Reschedule the Shrink Operation

• Cancel the active DBCC SHRINKFILE or DBCC SHRINKDATABASE session using KILL <SPID> once you confirm it is safe to terminate.
• Disable Auto-Shrink on the affected database via SSMS: Right-click database > Properties > Options > set “Auto Shrink” to False.
• Alternatively, run: ALTER DATABASE [YourDB] SET AUTO_SHRINK OFF;

Step 3: Allow the Online Index Build to Complete

• Monitor progress using SELECT percent_complete, estimated_completion_time FROM sys.dm_exec_requests WHERE command = 'ALTER INDEX';
• Avoid running shrink operations until percent_complete reaches 100 and the session clears from sys.dm_exec_requests.
• For very large LOB tables, consider using ALTER INDEX ... RESUME with MAX_DURATION and WAIT_AT_LOW_PRIORITY options (new in SQL Server 2025) to better control blocking windows.

Step 4: Re-run the Shrink Operation Sequentially

• Once the index rebuild completes, re-issue the shrink command in isolation: DBCC SHRINKFILE (N'YourDataFile', 0);
• Schedule shrink operations via SQL Agent during maintenance windows when no ONLINE = ON index operations are scheduled.
• Confirm no other resumable index operations are paused mid-build using sys.index_resumable_operations.

Step 5: Apply Latest Cumulative Update

• Verify your SQL Server 2025 build number via SELECT @@VERSION; and compare against the latest Cumulative Update (CU) on the Microsoft Update Catalog.
• Install the latest CU, as this error has known fixes related to LOB page-mapping race conditions in earlier SQL Server 2025 builds.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER after patching.

Alternative or Verification Steps

• Verify the fix by re-running DBCC SHRINKFILE against the same file after the index operation completes — it should finish without raising Error 5286, and check sys.dm_db_file_space_usage to confirm space was reclaimed.
• If the error persists outside of shrink operations, run DBCC CHECKDB with NO_INFOMSGS, ALL_ERRORMSGS to rule out actual page-level corruption as an underlying cause.

Troubleshooting Guide: SQL Server Error 5283 – Cross Rowset Check Failure on Nonclustered Columnstore Index

Why This Error Happens

Error 5283 occurs when SQL Server’s DBCC CHECKDB process detects that the row data stored across different rowgroups in a nonclustered columnstore index (NCCI) is inconsistent or misaligned, typically due to physical database corruption. This corruption usually stems from underlying storage issues, improper shutdowns, or disk-level failures that affected the delta store or compressed rowgroup segments. It can also occur if the index metadata became desynchronized due to a failed index rebuild operation or hardware-level bit flips.

Step-by-Step Resolution Guide

Step 1: Isolate and Confirm the Corruption Scope

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to get a full report of all affected objects.
• Note the specific Object ID and Index ID referenced in the Error 5283 message to confirm it is isolated to the columnstore index and not the base table.
• Check the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for accompanying I/O or storage warnings (e.g., Event ID 823 or 824).

Step 2: Verify Hardware and Storage Integrity

• Open Windows Server Manager and check the Event Viewer under Windows Logs > System for Disk or Ntfs warnings correlating with the time of the SQL error.
• If using Storage Spaces Direct or a SAN, run vendor-specific diagnostics to rule out physical disk degradation.
• Ensure the storage drivers are updated via Device Manager to prevent I/O path corruption in Windows Server 2025.

Step 3: Rebuild the Corrupted Columnstore Index

• Since columnstore indexes do not support single-page repair, you must drop and recreate the specific index rather than repairing it via REPAIR_ALLOW_DATA_LOSS.
• Execute ALTER INDEX [YourNCCIName] ON [YourTable] REBUILD; to force SQL Server to regenerate the segment data from the underlying clustered index or heap.
• If the rebuild fails due to heap-level corruption, you may need to restore the specific filegroup from a clean backup using RESTORE DATABASE... WITH PAGE.

Step 4: Restore from Backup if Rebuild Fails

• If the base table data is also corrupted, failover to your last known good full backup.
• Use RESTORE DATABASE YourDatabaseName FROM DISK = 'E:\Backups\YourBackup.bak' WITH REPLACE, RECOVERY; to restore the data.
• Apply any subsequent transaction log backups to minimize data loss up to the point of failure.

Alternative or Verification Steps

• Verify the Fix: Re-run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; immediately after the rebuild to ensure the 5283 error no longer appears and the consistency check returns 0 allocation errors.
• Alternative Solution: If the index continually fails to rebuild due to resource constraints, temporarily convert the columnstore index to a standard ROWSTORE (B-tree) index to stabilize the table, then attempt to re-create the columnstore structure during an off-peak maintenance window.

Why This Error Happens

Error 5282 occurs when SQL Server’s Storage Engine detects a broken or inconsistent LOB (Large Object) page chain for a table, usually surfaced during a DBCC CHECKDB or DBCC CHECKTABLE operation. This typically stems from physical database corruption caused by faulty storage hardware, an improperly terminated I/O operation (such as an unexpected shutdown), or a bug/bad sector affecting the underlying .mdf/.ndf file. In rarer cases, it can also result from antivirus or backup software locking database files mid-write on Windows Server 2025.

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 Object IDs.
• Note the exact Object ID and Index ID reported alongside Error 5282 for targeted repair later.
• Cross-reference the Object ID using SELECT OBJECT_NAME(object_id) FROM sys.objects WHERE object_id = <ID>;.

Step 2: Check Hardware and File System Health

• Open Event Viewer (eventvwr.msc) on Windows Server 2025 and inspect the System log for Disk or NTFS warnings around the time of corruption.
• Run chkdsk C: /f /r (adjust the drive letter to where your SQL data files reside) and schedule a restart if prompted.
• Verify your antivirus software has exclusions set for SQL Server data directories, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.

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

• Identify the last known good full backup taken before the corruption timestamp.
• Restore the database to a new name first using RESTORE DATABASE YourDB_Test FROM DISK = 'C:\Backups\YourDB.bak' WITH REPLACE; to validate integrity.
• Run DBCC CHECKDB against the restored copy to confirm it is clean before promoting it to production.

Step 4: Run Repair as a Last Resort

• If no clean backup exists, set the database to single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;.
• Execute DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS; — note this may delete corrupted rows to maintain structural consistency.
• Return the database to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;.

Alternative or Verification Steps

• Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; after remediation — a clean run with zero allocation or consistency errors confirms the fix.
• If corruption recurs frequently, move the database files to a different physical disk or virtual disk to rule out underlying storage/controller failure on the Windows Server 2025 host.

Troubleshooting Guide: SQL Server Error 5281 – TEMPDB Space Estimation

Why This Error Happens

Error 5281 is an informational message that occurs when SQL Server 2025 estimates that a large sort, index rebuild, or query operation will require more TEMPDB space (in KB) than is currently allocated or available. This typically happens because TEMPDB data files are undersized, autogrowth is disabled or limited, or the disk hosting TEMPDB has insufficient free space. It can also surface when a single large batch operation (such as an online index rebuild or a complex sort/hash join) spikes temporary storage demands beyond the configured TEMPDB ceiling.

Step-by-Step Resolution Guide

Step 1: Check Current TEMPDB Size and Configuration

• Run EXEC sp_helpdb 'tempdb'; in SSMS to review current file sizes and growth settings.
• Confirm the number of TEMPDB data files matches recommended guidance (typically 1 file per logical CPU core, up to 8).
• Check file paths under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ to ensure files are on fast, non-system storage.

Step 2: Increase TEMPDB File Size and Enable Autogrowth

• Use T-SQL to manually expand TEMPDB files:
• ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 8192MB, FILEGROWTH = 1024MB);
• Ensure autogrowth is set in MB (not percentage) to avoid uneven file growth across multiple TEMPDB files.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (Run as Administrator) if files were resized offline.

Step 3: Verify Disk Space and Storage Location

• Open File Explorer or run Get-PSDrive in PowerShell to confirm the drive hosting TEMPDB has at least 20–30% free space.
• If space is low, move TEMPDB files to a larger volume using:
• ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\SQLTempDB\tempdb.mdf');
• Restart the SQL Server service for the new file location to take effect.

Step 4: Review and Optimize the Triggering Query

• Use SQL Server Profiler or Extended Events to capture the exact query/operation causing the TEMPDB spike.
• Check execution plans for excessive sort/spool operators that could be optimized with better indexing.
• Consider breaking large batch operations (e.g., ALTER INDEX ALL REBUILD) into smaller, sequential steps to reduce peak TEMPDB demand.

Alternative or Verification Steps

• After applying changes, monitor TEMPDB usage in real time using SELECT * FROM sys.dm_db_file_space_usage; to confirm space allocation is no longer constrained.
• If the error persists under heavy load, consider enabling Trace Flag 1117 (equal file growth) and 1118 (reduce allocation contention) at the instance level, or migrate TEMPDB to a dedicated SSD/NVMe volume for improved I/O throughput.

Troubleshooting Guide: SQL Server Error 5280

Why This Error Happens

Error 5280 occurs when SQL Server’s consistency check engine (DBCC) encounters a malformed or interrupted communication packet during execution, typically caused by a disrupted connection between the client and the database engine mid-operation. This often results from network instability, TDS (Tabular Data Stream) protocol mismatches due to outdated client drivers, or memory pressure causing buffer corruption during large-scale CHECKDB operations. In some cases, antivirus or firewall software on Windows Server 2025 may intercept and alter packets in transit, triggering this protocol-level failure.

Step-by-Step Resolution Guide

Step 1: Verify Network Stability and Connection Integrity

• Check for packet loss between the client machine and the SQL Server instance using ping and tracert commands.
• Review the SQL Server error log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for corresponding network-related warnings.
• Disable any TCP Chimney Offload or RSS settings temporarily via Windows Network Adapter properties if packet corruption is suspected.

Step 2: Update SQL Server Client Drivers and Components

• Ensure the Microsoft ODBC Driver for SQL Server is updated to the latest version compatible with SQL Server 2025.
• Navigate to Control Panel > Programs > Programs and Features and verify the installed version of “Microsoft OLE DB Driver for SQL Server.”
• Run sqlcmd -S servername -U username -P password -Q "SELECT @@VERSION" to confirm the client-server handshake completes without protocol errors.

Step 3: Isolate and Exclude Security Software Interference

• Open Windows Defender Firewall with Advanced Security and confirm that SQL Server (sqlservr.exe) and TCP port 1433 are fully allowed for both inbound and outbound traffic.
• Temporarily disable third-party antivirus packet inspection features and re-run the DBCC command to see if the error persists.
• Add an exclusion rule in Windows Defender for the SQL Server data directory: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA.

Step 4: Re-run the Consistency Check with Reduced Load

• Execute DBCC CHECKDB('YourDatabaseName') WITH PHYSICAL_ONLY during a low-traffic window to reduce memory and I/O contention.
• Monitor Windows Task Manager or Performance Monitor (perfmon) for memory pressure spikes while the command executes.
• If the operation fails again, increase the max server memory setting slightly via SQL Server Management Studio under Server Properties > Memory to rule out buffer starvation.

Alternative or Verification Steps

• To verify the fix, re-run DBCC CHECKDB on the affected database and confirm it completes with “0 allocation errors and 0 consistency errors” in the output.
• If the error persists, restart the SQL Server service (net stop MSSQLSERVER followed by net start MSSQLSERVER) to clear any lingering TDS session corruption before retrying the consistency check.

Troubleshooting Guide: SQL Server Error 5279 – Table Error

Why This Error Happens

Error 5279 occurs when SQL Server’s consistency checker (DBCC CHECKDB/CHECKTABLE) detects structural inconsistencies in a table’s internal page linkage, typically caused by disk-level corruption, improper shutdowns, or faulty storage hardware. This error indicates that the page structure for the specified object ID does not match what SQL Server’s internal metadata expects, often due to torn writes or I/O subsystem failures. Underlying causes frequently include failing disks, antivirus interference with database files, or power loss during write operations on Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Object and Run Diagnostics

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run DBCC CHECKTABLE('YourTableName') WITH ALL_ERRORMSGS, NO_INFOMSGS; to confirm the object ID and scope of corruption.
• Cross-reference the object ID using SELECT OBJECT_NAME(object_id) FROM sys.objects WHERE object_id = <ObjectID>;

Step 2: Check Hardware and Storage Health

• Open Event Viewer (eventvwr.msc) on Windows Server 2025 and inspect the System log for Disk or NTFS warnings correlating with the SQL error timestamp.
• Run chkdsk C:\ /f /r (replace with your data drive letter) during a maintenance window to repair file system-level issues.
• Verify storage controller firmware and drivers are up to date via Device Manager or vendor tools (e.g., Dell OpenManage, HPE iLO).

Step 3: Restore from a Clean Backup (Preferred Fix)

• Navigate to your backup repository (e.g., D:\SQLBackups\) and identify the last known good full backup taken before corruption occurred.
• Restore using: RESTORE DATABASE [YourDB] FROM DISK = 'D:\SQLBackups\YourDB_Full.bak' WITH REPLACE, RECOVERY;
• If differential/log backups exist, apply them sequentially with WITH NORECOVERY until the final restore.

Step 4: Run Repair as a Last Resort (If No Backup Available)

• Set the database to single-user mode: ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Execute DBCC CHECKTABLE('YourTableName', REPAIR_ALLOW_DATA_LOSS); — note this may delete corrupted rows to restore structural integrity.
• Revert to multi-user mode: ALTER DATABASE YourDB SET MULTI_USER;

Alternative or Verification Steps

• Re-run DBCC CHECKTABLE('YourTableName') WITH NO_INFOMSGS; after remediation — a clean result with no error messages confirms the fix.
• If corruption recurs within days, treat it as a hardware-level red flag and engage your storage vendor or migrate the database to healthy storage immediately.

Troubleshooting Guide: SQL Server Error 5278 — DBCC Internal Database Snapshot LSN Mismatch

Why This Error Happens

Error 5278 occurs when DBCC commands (such as DBCC CHECKDB) create an internal database snapshot to perform consistency checks, but encounter a page whose Log Sequence Number (LSN) exceeds the current end of the transaction log. This typically indicates I/O subsystem instability, a crash-consistent but corrupted sparse file backing the snapshot, or concurrent write activity that outpaces the snapshot’s point-in-time view due to underlying storage driver issues. It can also surface when the database files reside on a filesystem or storage layer (such as ReFS or certain SAN/virtualization snapshot technologies) that fails to maintain proper write-ordering guarantees.

Step-by-Step Resolution Guide

Step 1: Validate Storage and I/O Subsystem Integrity

• Run chkdsk C: /f /r (or the relevant volume) on Windows Server 2025 / Windows 11 to rule out filesystem-level corruption.
• Check Windows Event Viewer under Windows Logs > System for Disk, ntfs, or storahci warnings around the time the error occurred.
• If running on a VM, confirm the hypervisor’s storage controller and disk drivers are fully updated (VMware Paravirtual SCSI or Hyper-V Synthetic SCSI).

Step 2: Run DBCC CHECKDB Without Snapshot Isolation

• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLOCK; — the TABLOCK option bypasses the internal database snapshot mechanism and uses table locks instead.
• Monitor for the same LSN-based failure; if it does not reoccur, this confirms the snapshot creation process (not the actual data) was the root issue.

Step 3: Verify Database File Placement and Snapshot Directory Space

• Confirm the drive hosting your database files (and tempdb, if using a custom snapshot directory) has sufficient free space, as internal snapshots use sparse files that grow dynamically.
• Check sys.database_files and confirm no files report state_desc = 'OFFLINE' or unexpected growth failures in the SQL Server Error Log (Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG).
• Move database files off of any third-party backup-driven snapshot or replication software temporarily to test for conflicts.

Step 4: Restore From a Known Good Backup if Corruption Persists

• If DBCC CHECKDB with TABLOCK reports actual allocation or consistency errors (not just the 5278 snapshot issue), restore the database from the last verified clean full backup plus transaction log backups.
• Use RESTORE VERIFYONLY FROM DISK = 'BackupPath.bak'; before restoring to confirm backup integrity first.

Step 5: Update SQL Server 2025 Cumulative Updates

• Navigate to SQL Server Management Studio > Help > About to confirm your current build number.
• Download and apply the latest Cumulative Update (CU) from the Microsoft Update Catalog, as LSN-handling bugs in snapshot creation are periodically patched.

Alternative or Verification Steps

• Re-run DBCC CHECKDB during a low-activity maintenance window to confirm the error does not reoccur under reduced transactional load.
• If the issue persists only under high concurrency, consider scheduling DBCC CHECKDB jobs during off-peak hours or isolating the instance from external storage-level snapshot tools (SAN replication, VSS-based backups) during the check.

Troubleshooting Guide: SQL Server Error 5277 – Database Snapshot Split Point LSN Mismatch

Why This Error Happens

Error 5277 occurs when SQL Server’s internal database snapshot mechanism detects that the split point Log Sequence Number (LSN) does not align correctly with the first LSN recorded in the sparse file chain. This typically happens due to corruption in the sparse file used for the database snapshot, an unexpected shutdown during a snapshot-based operation (such as DBCC CHECKDB or a snapshot-based backup), or underlying storage-level inconsistencies on Windows Server 2025 that prevent proper synchronization of the snapshot’s transaction log metadata. In rare cases, this can also surface when antivirus or filesystem-level filters interfere with sparse file I/O on NTFS/ReFS volumes.

Step-by-Step Resolution Guide

Step 1: Identify and Remove the Corrupted Database Snapshot

• Run SELECT name, source_database_id FROM sys.databases WHERE source_database_id IS NOT NULL; to list active snapshots.
• Drop the affected snapshot using DROP DATABASE [YourSnapshotName];
• Confirm no automated jobs (e.g., DBCC CHECKDB internal snapshots) are still referencing the sparse file in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.

Step 2: Validate Storage and Filesystem Integrity

• Open Event Viewer (eventvwr.msc) and check the System log for disk-related Event IDs (51, 153) around the same timestamp as Error 5277.
• Run chkdsk C: /f /r (adjust drive letter to your data volume) during a maintenance window to repair NTFS/ReFS inconsistencies.
• Temporarily disable third-party antivirus real-time scanning on the SQL data directory to rule out file-locking interference with sparse files.

Step 3: Recreate the Snapshot and Re-run the Originating Operation

• Manually create a fresh snapshot using CREATE DATABASE YourSnapshotName ON (NAME = YourDB, FILENAME = 'C:\SQLData\YourSnapshot.ss') AS SNAPSHOT OF YourDB;
• Re-execute the operation that triggered the error, such as DBCC CHECKDB('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Monitor the SQL Server Error Log (SSMS > Management > SQL Server Logs) for recurrence of Event ID 5277.

Step 4: Restore from a Known Good Backup (If Corruption Persists)

• If the snapshot mechanism continues to fail, restore the source database from the last verified full backup using RESTORE DATABASE YourDB FROM DISK = 'D:\Backups\YourDB.bak' WITH RECOVERY;
• Apply any subsequent transaction log backups in sequence to minimize data loss.
• Validate database consistency post-restore with DBCC CHECKDB before resuming production workloads.

Alternative or Verification Steps

• Verify resolution by running DBCC CHECKDB with no errors returned and confirming the SQL Server Error Log shows no further 5277 entries over a 24-hour monitoring period.
• If the issue persists despite storage repairs, open a Microsoft support case referencing Event ID 5277 and attach the SQLDIAG output along with the Windows Server 2025 System/Application event logs for deeper analysis of potential storage driver or filesystem filter conflicts.

Troubleshooting Guide: SQL Server Error 5276 – Exhaustive Search Failure During Consistency Check

Why This Error Happens

Error 5276 occurs when DBCC CHECKDB’s exhaustive search algorithm encounters a severe internal exception while attempting to locate inconsistencies in a database, typically because the underlying page structures are too corrupted for the standard search logic to parse. This is most commonly triggered by physical disk-level corruption, incomplete I/O writes due to a storage subsystem fault, or memory pressure causing the search buffer to fail mid-operation. In rare cases, it can also stem from a bug in the exhaustive search routine itself when handling unusually fragmented or malformed allocation structures introduced after an improper shutdown.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope and Severity of Corruption

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; from SQL Server Management Studio or sqlcmd to capture the full error context.
• Check the SQL Server error log (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating I/O or storage-related warnings around the same timestamp.
• Note the exact exception number reported alongside Error 5276, as this helps determine if it’s a memory access violation or an assertion failure.

Step 2: Validate Storage Integrity at the OS Level

• Open Windows Server 2025 Event Viewer and navigate to Windows Logs > System to check for Disk or NTFS warnings (Event IDs 7, 51, or 153).
• Run chkdsk C: /f /r (adjust the drive letter to match your data file location) and schedule a restart if the volume is in use.
• If using Storage Spaces Direct or a SAN, verify the LUN health via Get-PhysicalDisk | Get-StorageReliabilityCounter in PowerShell to rule out failing hardware.

Step 3: Restore from a Known-Good Backup

• Identify the last verified clean backup using RESTORE VERIFYONLY FROM DISK = 'E:\Backups\YourDatabase.bak';.
• Perform the restore to a new database name first to avoid overwriting the corrupted instance:
  RESTORE DATABASE YourDatabase_Test FROM DISK = 'E:\Backups\YourDatabase.bak' WITH MOVE 'LogicalDataName' TO 'E:\Data\YourDatabase_Test.mdf', MOVE 'LogicalLogName' TO 'E:\Data\YourDatabase_Test.ldf';
• Once validated with DBCC CHECKDB on the restored copy, swap it into production during a maintenance window.

Step 4: Apply Repair Only as a Last Resort

• If no clean backup exists, run DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS; only after setting the database to single-user mode via ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;.
• Understand this step may delete corrupted rows or pages permanently, so export critical data beforehand if the database is still partially readable.

Alternative or Verification Steps

• After remediation, re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; and confirm zero allocation or consistency errors are returned.
• If Error 5276 persists even after storage validation and backup restoration, open a Microsoft Support case referencing the specific exception code, as it may indicate an unpatched CU-level bug requiring a hotfix for SQL Server 2025.

Troubleshooting Guide: Error 5275 – Exhaustive Search Consistency Check Completion

Why This Error Happens

Error 5275 is not actually an error — it is an informational message logged by SQL Server’s DBCC CHECKDB process when it performs an exhaustive search algorithm to locate inconsistencies in database structures, typically triggered after a prior corruption event or when standard search methods fail to resolve allocation discrepancies. This message appears in the SQL Server Error Log simply to confirm that the exhaustive search completed, how many search iterations were processed, and how long the operation took. It often surfaces alongside real corruption issues (like torn pages, allocation errors, or disk-level faults) because the exhaustive search is only invoked when SQL Server’s internal consistency checker needs a deeper pass to verify object integrity.

Step-by-Step Resolution Guide

Step 1: Confirm This Is Informational, Not a Failure

• Open the SQL Server Error Log via SSMS: Management > SQL Server Logs.
• Search for Event ID 5275 and read the full message context — check if it’s paired with other errors (like 8928, 8944, or 824) which indicate actual corruption.
• If 5275 appears alone with no accompanying error codes, no action is required.

Step 2: Run a Full DBCC CHECKDB to Validate Database Health

• Open a query window and execute:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Review the output for allocation errors, consistency errors, or repair recommendations.
• If corruption is detected, note the recommended minimum repair level (e.g., REPAIR_REBUILD or REPAIR_ALLOW_DATA_LOSS).

Step 3: Check Underlying Storage and I/O Subsystem

• Navigate to Windows Event Viewer > Windows Logs > System and filter for Disk, NTFS, or Storport warnings.
• Run chkdsk C: /f /r (or the relevant drive letter hosting your .mdf/.ldf files) during a maintenance window.
• Verify SAN/NAS or virtual disk health if running on Windows Server 2025 in a virtualized or cloud environment.

Step 4: Apply Repair Options Only If Corruption Is Confirmed

• If DBCC CHECKDB flagged actual errors, take a full backup first:

  BACKUP DATABASE [YourDatabaseName] TO DISK = 'D:\Backups\YourDatabaseName_PreRepair.bak';

• Set the database to single-user mode before repair:

  ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

• Run the minimum required repair level identified in Step 2, then restore multi-user access:

  ALTER DATABASE [YourDatabaseName] SET MULTI_USER;

Step 5: Update SQL Server 2025 Cumulative Updates

• Check Microsoft’s servicing page for the latest Cumulative Update (CU) for SQL Server 2025.
• Apply the update via Setup.exe from an elevated command prompt, as some exhaustive search behaviors are refined in later builds to reduce false-positive triggers.

Alternative or Verification Steps

• Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; after Step 4 — a clean result with zero allocation or consistency errors confirms the fix.
• If corruption recurs frequently, enable Extended Events to capture I/O latency and page checksum failures, which often point to a failing physical disk requiring hardware replacement rather than repeated SQL-level repairs.

Why This Error Happens

Error 5274 typically occurs when SQL Server’s internal metadata linking system objects (such as columns, indexes, or constraints) becomes inconsistent, often due to abrupt shutdowns, storage-level corruption, or disk I/O failures during write operations. This can also surface after a failed in-place upgrade to SQL Server 2025 where system catalog views were not fully migrated. In most cases, the underlying data pages are intact, but the structural pointers connecting objects in sys.objects or sys.columns are broken.

Step-by-Step Resolution Guide

Step 1: Isolate and Confirm the Scope of Corruption

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOREPAIR, ALL_ERRORMSGS, DATA_PURITY; from SQL Server Management Studio (SSMS) 21.
• Note the specific Object ID (%d) reported in the error and cross-reference it using SELECT OBJECT_NAME(object_id) FROM sys.objects WHERE object_id = <ID>;.
• Check the Windows Event Viewer (Event Viewer > Windows Logs > Application) for correlating Disk or NTFS errors around the same timestamp.

Step 2: Take a Clean Backup Before Repair

• Immediately back up the database, even in its corrupted state, to a separate drive: BACKUP DATABASE YourDatabaseName TO DISK = 'D:\Backups\PreRepair.bak';.
• This step is critical because repair operations are destructive and cannot be undone once applied.

Step 3: Execute Targeted Repair Commands

• 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 the Object ID belongs to a non-critical user table.
• Revert to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;.

Step 4: Validate Storage Hardware and Drivers

• On Windows Server 2025, open Server Manager > Tools > Defragment and Optimize Drives and run a consistency check on the volume hosting your .mdf files.
• Update storage controller drivers via Device Manager, as outdated NVMe or RAID drivers are a known trigger for silent page corruption in Windows 11/Server 2025 environments.

Alternative or Verification Steps

• Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOREPAIR; after the repair; a clean result with “0 allocation errors and 0 consistency errors” confirms the fix.
• If Error 5274 persists, restore the database from the last known-good backup prior to the corruption event rather than relying on repeated repair attempts, as repeated REPAIR_ALLOW_DATA_LOSS runs on the same object can lead to further data integrity issues.

Troubleshooting Guide: Error 5273 – Page Could Not Be Moved During Online Index Build

Why This Error Happens

Error 5273 occurs when SQL Server’s online page relocation mechanism (used by auto-shrink, DBCC SHRINKDATABASE, or DBCC SHRINKFILE) attempts to move a data page that is currently owned by an index or heap undergoing an online index build/rebuild operation. Because online index operations maintain their own transient page allocations and version-store tracking to allow concurrent user access, the shrink/defrag thread cannot safely relocate those pages until the build finishes. This is a concurrency protection mechanism rather than corruption — it signals a collision between two background maintenance operations racing for the same allocation unit.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Online Index Operation

• Query active online index builds using the DMV:

  SELECT session_id, command, percent_complete, start_time 
  FROM sys.dm_exec_requests 
  WHERE command LIKE '%INDEX%';

• Cross-reference with sys.dm_exec_sql_text(sql_handle) to confirm which table/index is being rebuilt online.
• Note the session_id and estimated percent_complete to gauge remaining duration.

Step 2: Pause or Reschedule the Competing Shrink Operation

• Immediately cancel any active shrink job triggered via SQL Agent or manual DBCC SHRINKFILE / DBCC SHRINKDATABASE execution:

  KILL <shrink_session_id>;

• Disable Auto Shrink on the affected database to prevent automatic re-triggering:

  ALTER DATABASE [YourDB] SET AUTO_SHRINK OFF;

• Reschedule shrink/defrag maintenance plans to run only after index maintenance windows complete (edit via SQL Server Agent → Jobs → Maintenance Plan schedules).

Step 3: Allow the Online Index Build to Complete Uninterrupted

• Let the ALTER INDEX ... REBUILD WITH (ONLINE = ON) operation finish naturally; avoid issuing concurrent DDL or shrink commands on the same filegroup.
• On Windows Server 2025/Windows 11 hosts, verify sufficient tempdb space in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Data\ since online rebuilds rely heavily on tempdb for row versioning.
• Monitor sys.dm_exec_requests until percent_complete reaches 100 and the session clears.

Step 4: Re-run the Shrink Operation Sequentially

• After confirming no active index builds remain, re-issue the shrink command safely:

  DBCC SHRINKFILE (N'YourDB_Data', TRUNCATEONLY);

• Avoid running shrink operations during peak index maintenance windows going forward.

Alternative or Verification Steps

• Verify resolution by re-running the original shrink command — Error 5273 should no longer appear in the SQL Server Error Log (Application event log or ERRORLOG file under MSSQL\Log\).
• If the error persists, run DBCC CHECKDB with NO_INFOMSGS to rule out underlying allocation page corruption rather than a scheduling conflict.

Troubleshooting Guide: Error 5272 – IAM Page Could Not Be Moved

Why This Error Happens

Error 5272 occurs when SQL Server’s DBCC SHRINKFILE or auto-shrink operation attempts to relocate an Index Allocation Map (IAM) page but cannot obtain the required exclusive lock on the underlying table or index. This typically happens because another session holds an active transaction, a long-running query, or a schema lock on the object at the exact moment the shrink operation tries to move data pages. Concurrent DML operations (INSERT/UPDATE/DELETE), open transactions, or replication/CDC processes reading from the same object are the most common culprits blocking exclusive access.

Step-by-Step Resolution Guide

Step 1: Identify Blocking Sessions and Active Transactions

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run sp_who2 or query sys.dm_exec_requests to find sessions with open transactions against the target database.
• Check sys.dm_tran_locks filtered by resource_database_id to identify which object is holding the exclusive lock request hostage.
• Note the session_id of any long-running transactions tied to the table reported in the error.

Step 2: Schedule the Shrink Operation During Low-Activity Windows

• Avoid running DBCC SHRINKFILE or DBCC SHRINKDATABASE during business hours or peak ETL/batch processing windows.
• On Windows Server 2025, use Task Scheduler or SQL Server Agent Jobs to trigger shrink operations during a maintenance window (e.g., 2:00 AM – 4:00 AM).
• Example command: DBCC SHRINKFILE (N'YourDataFile_log', 10) WITH NO_INFOMSGS;
• Ensure no overlapping index maintenance jobs (REBUILD/REORGANIZE) are scheduled at the same time, as they also require exclusive access.

Step 3: Disable or Pause Auto-Shrink if Enabled

• Open SSMS, right-click the database → Properties → Options.
• Locate “Auto Shrink” and set it to False (auto-shrink is not recommended in production environments).
• Alternatively, run: ALTER DATABASE [YourDB] SET AUTO_SHRINK OFF;
• This prevents SQL Server from attempting unpredictable shrink operations while transactions are active.

Step 4: Isolate the Object and Retry Manually

• If a specific table is repeatedly flagged, place the database in single-user mode temporarily (only if feasible): ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Re-run the shrink command manually once no other connections are active.
• Revert back to multi-user mode immediately after: ALTER DATABASE [YourDB] SET MULTI_USER;

Step 5: Check for Replication, CDC, or Always On Availability Group Interference

• Query sys.dm_cdc_log_scan_sessions if Change Data Capture is enabled on the table.
• If using Always On Availability Groups, confirm the secondary replica isn’t holding a read-only lock via redo operations.
• Temporarily pause CDC capture jobs or replication log reader agents before retrying the shrink.

Alternative or Verification Steps

• To verify the fix, re-run the original DBCC SHRINKFILE command and confirm it completes without Error 5272 in the SQL Server Error Log (Event Viewer → Applications and Services Logs → MSSQLSERVER).
• If the error persists, consider rebuilding the specific index with ALTER INDEX ALL ON [TableName] REBUILD; during a maintenance window instead of shrinking the entire file, as this often resolves fragmentation-related locking without requiring exclusive database-wide access.

Troubleshooting Guide: DBCC Error 5271

Why This Error Happens

Error 5271 occurs when DBCC commands (such as DBCC CHECKDB, CHECKTABLE, or CHECKALLOC) encounter an internal failure while attempting to format or output diagnostic results, rather than a failure in the actual consistency check itself. This is commonly triggered by corrupted tempdb system objects, insufficient memory to buffer the output results, or restrictive permissions on the service account that prevent writing to the output stream or trace destination. In some cases, this also stems from a corrupted internal results buffer caused by an underlying hardware-level I/O issue or a damaged SQL Server installation file.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server Error Log for Underlying Errors

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Look for errors immediately preceding Error 5271, such as 823, 824, or 701 (out of memory), which indicate the true root cause.
• Alternatively, run EXEC sp_readerrorlog 0, 1, '5271'; to filter directly for related entries.

Step 2: Check and Rebuild tempdb Health

• Since DBCC commands rely heavily on tempdb for internal sorting and output staging, verify tempdb is not full or corrupted.
• Run DBCC CHECKDB (tempdb) WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm tempdb integrity.
• If tempdb files are undersized, expand them manually via: ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 5120MB);
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to force tempdb to rebuild cleanly.

Step 3: Verify Service Account Permissions and Disk Space

• Confirm the SQL Server service account (visible in SQL Server Configuration Manager) has full read/write access to the data and log directories, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA.
• Check available disk space on the drive hosting tempdb and the database being checked; low disk space can prevent DBCC from writing output buffers.
• Ensure Windows 11/Server 2025 User Account Control (UAC) or antivirus software isn’t blocking SQL Server’s write operations to these paths.

Step 4: Run DBCC Command with Reduced Output Scope

• Isolate the issue by running the DBCC command against a single table instead of the full database: DBCC CHECKTABLE ('YourTableName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• If this succeeds, the issue may be tied to result set size or a specific corrupted object rather than a system-wide failure.

Step 5: Repair or Reinstall SQL Server Components

• If errors persist, use the SQL Server 2025 Installation Center to run a Repair operation on the instance (Control Panel > Programs > Microsoft SQL Server 2025 > Change > Repair).
• This resolves issues caused by corrupted binaries or DLLs tied to the DBCC output formatting engine.

Alternative or Verification Steps

• After applying fixes, re-run the original DBCC command (e.g., DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;) and confirm it completes without Error 5271 or related 823/824 errors in the log.
• As an alternative, redirect output using DBCC CHECKDB ('YourDatabase') WITH TABLERESULTS and query the results into a temporary table to bypass potential console/output buffer limitations.

Troubleshooting Guide: SQL Server Error 5270 – Unmovable Page in Critical System Table

Why This Error Happens

Error 5270 occurs when SQL Server’s DBCC SHRINKFILE or auto-shrink operation attempts to relocate a page that belongs to a critical system table, which is physically “pinned” to its location and cannot be moved during the compaction process. This typically happens because certain system catalog pages (such as those backing sys.sysschobjs, sys.sysrowsets, or other base system tables) have hard dependencies that prevent relocation within the data file. The issue is frequently triggered by attempting to shrink a database file that is already near its minimum possible size, or when the database was upgraded from an older SQL Server version with legacy system table structures still present.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Database and File

• Capture the full error message, noting the file ID and page ID referenced (e.g., Page 1:152).
• Run the following to identify which database and logical file triggered the error:

SELECT DB_NAME(), name, physical_name FROM sys.database_files;

• Confirm whether the shrink was manual (DBCC SHRINKFILE) or triggered by auto-shrink.

Step 2: Disable Auto-Shrink and Avoid Aggressive Shrink Operations

• Check if auto-shrink is enabled on the database:

SELECT name, is_auto_shrink_on FROM sys.databases WHERE name = 'YourDatabaseName';

• Disable it to prevent recurring failures:

ALTER DATABASE YourDatabaseName SET AUTO_SHRINK OFF;

• Avoid running DBCC SHRINKFILE with TRUNCATEONLY or aggressive target sizes on data files containing system catalogs.

Step 3: Rebuild Indexes to Consolidate Pages Before Shrinking

• Rebuild clustered indexes on user tables first to reduce fragmentation and reclaim space logically rather than relying on shrink to compact physically:

ALTER INDEX ALL ON YourTableName REBUILD WITH (ONLINE = ON, MAXDOP = 4);

• After rebuilding major indexes, retry the shrink operation targeting a more conservative free space percentage (leave at least 10-15% free).

Step 4: Use a File Group Migration Instead of Direct Shrink

• If the page remains unmovable, create a new filegroup and migrate user tables/indexes to it:

ALTER DATABASE YourDatabaseName ADD FILEGROUP FG_New;
ALTER DATABASE YourDatabaseName ADD FILE (NAME = 'NewDataFile', FILENAME = 'D:\SQLData\NewDataFile.ndf') TO FILEGROUP FG_New;

• Rebuild indexes onto the new filegroup, then shrink the original (now emptier) primary data file, which reduces the chance of hitting pinned system pages.

Step 5: Validate Database Consistency

• Run DBCC CHECKDB to rule out underlying corruption as a contributing factor:

DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Address any corruption found before attempting further shrink operations.

Alternative or Verification Steps

• Re-run DBCC SHRINKFILE (YourFileName, TargetSizeMB) after completing the steps above and confirm it completes without Error 5270 in the SQL Server Error Log or sys.messages.
• If the error persists specifically on system tables, consider leaving the file at its current size (shrinking system-heavy primary files is not recommended for production databases) and instead manage growth proactively through pre-sized files and regular index maintenance.

Troubleshooting Guide: Error 5269 – Transient Database Snapshot Marked Suspect

Why This Error Happens

Error 5269 occurs when SQL Server’s internal consistency checker (DBCC CHECKDB) creates a hidden, transient database snapshot to perform validation, but the underlying I/O subsystem fails while reading or writing sparse file data for that snapshot. This is typically caused by insufficient disk space on the volume hosting the snapshot sparse files, underlying storage corruption, antivirus/filter driver interference, or a failing disk/storage controller. Since the snapshot relies on NTFS sparse file technology, any interruption to the volume (permissions, storage latency, or disk health) during the CHECKDB operation causes the snapshot to be marked suspect and the check to terminate.

Step-by-Step Resolution Guide

Step 1: Verify Disk Space and Volume Health

• Check the volume where the database data files reside (snapshot files are created in the same directory by default) for available free space.
• Run Get-Volume in PowerShell or check Disk Management to confirm at least 2x the size of the largest data file is free.
• Run chkdsk C: /f /r (replace with the correct drive letter) during a maintenance window to rule out underlying file system corruption.

Step 2: Review the SQL Server Error Log for Root Cause

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs, or review the log file directly at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Look for accompanying OS-level errors such as Error 823, 824, or 825, which indicate specific I/O failures (e.g., “incorrect checksum” or “operating system error 1117”).
• Note the specific file path mentioned for the sparse snapshot file (usually named with a .ss extension in the default data directory).

Step 3: Exclude SQL Server Directories from Antivirus/Filter Drivers

• Open Windows Security or your third-party antivirus console on Windows Server 2025.
• Add exclusions for the SQL Server data directory (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA) and the tempdb location.
• Restart the SQL Server (MSSQLSERVER) service via services.msc to ensure the exclusion takes effect before re-running the check.

Step 4: Run DBCC CHECKDB with Alternative Options

• Instead of allowing SQL Server to create an automatic snapshot, force the check to run directly against the live database using the WITH TABLOCK option, which bypasses the snapshot creation mechanism:

DBCC CHECKDB ('YourDatabaseName') WITH TABLOCK, NO_INFOMSGS;

• Note that this will acquire locks and may block user activity, so schedule it during off-peak hours.

Step 5: Inspect Physical Storage and Drivers

• Update storage controller and HBA drivers via Windows Update or the hardware vendor’s site, specifically for Windows Server 2025 compatibility.
• Check the Windows Event Viewer under Windows Logs > System for Disk or Ntfs warnings that correlate with the timestamp of the SQL error.

Alternative or Verification Steps

• Verify the Fix: Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; and confirm it completes without Error 5269 or 823/824 I/O errors in the output.
• Alternative Solution: If the issue persists on the same volume, move the database files (ALTER DATABASE ... MODIFY FILE) to a different physical disk or LUN to isolate whether the storage hardware is the root cause.

Troubleshooting Guide: SQL Server Error 5268 — DBCC Exhaustive Index Search

Why This Error Happens

Error 5268 is triggered when DBCC CHECKDB, CHECKTABLE, or CHECKFILEGROUP cannot use the standard internal index-to-data linkage shortcuts to validate consistency, forcing SQL Server to perform a full exhaustive scan of every index instead. This typically occurs after a database upgrade, a restore from an older compatibility level, or when sparse/filtered indexes and certain data types prevent the fast-path consistency check logic from being used. Despite its alarming wording, this is purely an informational message (Severity 10) and does not indicate corruption by itself — it only means the check will take longer to complete.

Step-by-Step Resolution Guide

Step 1: Confirm the Message Is Informational, Not an Error

• Check the full DBCC output — Error 5268 carries Severity Level 10, which is informational, not a failure.
• Look for accompanying Severity 16+ errors (like 8928 or 8944) which would indicate actual corruption requiring action.
• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS to filter out informational noise.

Step 2: Allow the Exhaustive Check to Complete

• Avoid canceling the DBCC job mid-run, as exhaustive index checks on large databases can take significantly longer (sometimes hours on multi-TB tables).
• Monitor progress using SQL Server Management Studio (SSMS) Activity Monitor or query sys.dm_exec_requests for the percent_complete column tied to the DBCC session ID.
• Ensure the SQL Server Agent job or maintenance window has sufficient time allocated (adjust job schedules in SQL Server Agent > Jobs > Properties > Schedules if it’s timing out prematurely).

Step 3: Rule Out Compatibility Level or Index Type Triggers

• Check the database compatibility level with: SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
• If the database was recently migrated from SQL Server 2016/2019 to 2025, verify filtered indexes and computed columns are rebuilt correctly using ALTER INDEX ALL ON [TableName] REBUILD;
• Rebuilding fragmented or outdated indexes often eliminates the need for the exhaustive fallback on subsequent checks.

Step 4: Optimize Resource Allocation for Large-Scale Checks

• Increase tempdb capacity, since exhaustive DBCC scans use tempdb heavily for internal sorting; navigate to SQL Server Configuration Manager > SQL Server Services > tempdb file properties to pre-size tempdb files.
• Set max memory allocation appropriately via sp_configure 'max server memory (MB)' to prevent memory pressure during the scan on Windows Server 2025 hosts.
• Consider running the check during off-peak hours using Windows Task Scheduler or SQL Agent to minimize I/O contention.

Alternative or Verification Steps

• Re-run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS after completion — if no Severity 16+ errors appear, the database is healthy and Error 5268 can be safely disregarded.
• If exhaustive checks persistently trigger on every run, update statistics (UPDATE STATISTICS on affected tables) and ensure all indexes are rebuilt, as stale statistics are a common alternative cause of the fast-path check being skipped.

Troubleshooting Guide: SQL Server Error 5267 — Row Count Mismatch

Why This Error Happens

Error 5267 occurs when DBCC CHECKTABLE or DBCC CHECKDB detects that the row count stored in the system metadata (sys.partitions or sys.sysindexes) does not match the actual number of rows physically present in the table or index. This discrepancy is typically caused by corrupted index statistics, an improperly terminated transaction during a bulk insert/delete operation, or disk-level corruption affecting the storage engine’s internal tracking tables. It can also surface after an unclean shutdown, hardware failure, or a failed failover on Windows Server 2025 clustered environments where metadata updates were not fully committed before the crash.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of Corruption

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; from SQL Server Management Studio (SSMS) or sqlcmd.
• Note the exact table name and object ID referenced alongside Error 5267 in the output.
• Check the SQL Server error log (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating I/O warnings or Event ID 824/825 entries.

Step 2: Rebuild Statistics and Update Metadata

• Execute UPDATE STATISTICS YourTableName WITH FULLSCAN; on the affected table to force SQL Server to recalculate row counts accurately.
• If the issue persists, rebuild all indexes on the table using ALTER INDEX ALL ON YourTableName REBUILD; — this forces a full metadata refresh in sys.partitions.
• For heap tables without clustered indexes, run DBCC UPDATEUSAGE('YourDatabaseName', 'YourTableName') WITH COUNT_ROWS; to correct the sys.sysindexes/sys.partitions row count values directly.

Step 3: Run a Targeted Consistency Repair

• If rebuilding does not resolve it, isolate the database and run DBCC CHECKDB('YourDatabaseName', REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS; (does not require data loss).
• Only use REPAIR_ALLOW_DATA_LOSS as a last resort if REPAIR_REBUILD fails, and only after taking a full backup first.
• Restart the SQL Server service via Services.msc (services.msc) or net stop MSSQLSERVER && net start MSSQLSERVER to clear any cached metadata locks from Windows Server 2025’s memory manager.

Step 4: Validate Underlying Storage Health

• Check Windows Event Viewer (eventvwr.msc) under Windows Logs > System for Disk or NTFS warnings correlating with the SQL Server error timestamp.
• Run chkdsk C: /f /r (adjust drive letter to your data file volume) during a scheduled maintenance window to rule out physical disk corruption as the root cause.

Alternative or Verification Steps

• Re-run DBCC CHECKTABLE('YourTableName') WITH NO_INFOMSGS, ALL_ERRORMSGS; after remediation — a clean result with no Error 5267 output confirms the row count mismatch is resolved.
• If the error recurs frequently on the same table, restore from the most recent verified full backup and replay transaction logs instead of repeated repairs, as persistent 5267 errors often indicate degrading storage hardware.

Troubleshooting Guide: SQL Server Error 5266 – RSVD Page Count Mismatch

Why This Error Happens

Error 5266 occurs when SQL Server’s DBCC CHECKDB (or CHECKALLOC) detects a discrepancy between the reserved (RSVD) page count recorded in the system catalog (sys.allocation_units) and the actual number of pages physically allocated to an object. This typically stems from corrupted allocation metadata (IAM/GAM/SGAM pages) caused by abrupt shutdowns, storage-level I/O failures, or a bug in internal page tracking during large bulk operations. It can also surface after improper VSS-based snapshot backups or SAN replication that captured an inconsistent state of the database files on Windows Server 2025 storage volumes.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of Corruption

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; from SQL Server Management Studio (SSMS) or sqlcmd.
• Review the output carefully to confirm Error 5266 is isolated to allocation metadata and not accompanied by other critical errors (e.g., 8928, 8944), which would indicate deeper page-level corruption.
• Note the exact object ID and index ID referenced in the error message for targeted repair.

Step 2: Rule Out Storage and Hardware Issues

• Check the Windows Event Viewer (eventvwr.msc) under Windows Logs > System for Disk or NTFS warnings correlating with the SQL error timestamp.
• Run chkdsk C:\ /f /r (adjust drive letter to your data/log file path) during a maintenance window to validate underlying disk integrity on Windows Server 2025.
• Verify that antivirus or VSS snapshot tools are not interfering with .mdf/.ldf files, and exclude the SQL Server data directory (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) from real-time scanning.

Step 3: Attempt a Targeted Repair

• If corruption is confirmed only in allocation pages, run: DBCC CHECKDB('YourDatabaseName', REPAIR_REBUILD);
• If REPAIR_REBUILD does not resolve it, take a full backup first, then run the more aggressive option: DBCC CHECKDB('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);
• Execute this only on a copy of the database if possible, since REPAIR_ALLOW_DATA_LOSS can delete pages to resolve allocation mismatches.

Step 4: Restore From a Clean Backup (If Repair Fails)

• Locate your most recent verified-clean full backup, typically stored on your backup server or Azure Blob target.
• Restore it using: RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabaseName.bak' WITH REPLACE;
• Apply subsequent transaction log backups if point-in-time recovery is required, and re-run DBCC CHECKDB post-restore to confirm integrity.

Alternative or Verification Steps

• After applying a fix, re-run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; and confirm it returns “0 allocation errors and 0 consistency errors.”
• If the error recurs on the same object after repair, consider rebuilding the affected index entirely using ALTER INDEX ALL ON YourTableName REBUILD; or migrating the table data to a new object via SELECT INTO as a last-resort alternative.

Troubleshooting Guide: Error 5265 – USED Pages Mismatch in SQL Server 2025

Why This Error Happens

Error 5265 occurs when DBCC CHECKALLOC detects a discrepancy between the number of used pages recorded in the system metadata (sys.allocation_units or sys.sysindexes) and the actual number of pages physically allocated within the data file. This mismatch is typically caused by database corruption resulting from an unexpected shutdown, storage subsystem failures (disk, SAN, or controller errors), or a bug triggered during a page allocation/deallocation sequence that was interrupted mid-transaction. In rare cases, running SQL Server 2025 on Windows Server 2025 with outdated storage drivers or filesystem-level corruption (NTFS/ReFS) can also trigger a false positive during the allocation scan.

Step-by-Step Resolution Guide

Step 1: Isolate the Database and Verify Corruption Scope

• Place the affected database in single-user mode or take it offline for write operations to prevent further corruption:

  ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

• Run a full consistency check to capture the complete scope of the error before attempting repairs:

  DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Note the specific Page IDs and Object IDs referenced in the 5265 output for cross-verification later.

Step 2: Check Underlying Hardware and Storage Drivers

• Navigate to Device Manager on Windows Server 2025 and verify Storage Controllers for pending driver updates.
• Check the Windows Event Viewer (Windows Logs > System) for Event ID 153 or 157, which indicate I/O retries or disk timeout issues that often correlate with SQL allocation errors.
• If using Storage Spaces Direct or a SAN, run Get-PhysicalDisk in PowerShell to ensure the HealthStatus is reported as “Healthy.”

Step 3: Attempt Repair Using DBCC CHECKALLOC

• If the corruption is isolated to allocation pages only (and not user data), run the specific allocation repair command:

  DBCC CHECKALLOC ('YourDB', REPAIR_ALLOW_DATA_LOSS);

• Caution: This command may result in data loss for the specific pages that are incorrectly allocated; ensure a backup exists prior to execution.
• Monitor the output grid for a return code of 0, confirming the repair executed without further systemic errors.

Step 4: Restore From a Clean Backup (If Repair Fails)

• If REPAIR_ALLOW_DATA_LOSS does not resolve the discrepancy, the fastest path to a stable state is restoring the last known good full backup.
• Use SQL Server Management Studio (SSMS) or T-SQL to restore the .bak file to a new database name to verify integrity before overwriting production:

  RESTORE DATABASE [YourDB_Test] FROM DISK = 'C:\Backups\YourDB.bak' WITH NORECOVERY;

• Apply any available transaction log backups to minimize data loss up to the point of failure.

Alternative or Verification Steps

• Verification: After repair or restoration, re-run DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS to confirm the output returns “0 allocation errors and 0 consistency errors.”
• Alternative: If corruption persists across multiple nightly checks, migrate the database files to a different physical volume (via ALTER DATABASE MODIFY FILE) to rule out a specific faulty sector on the current disk array.

Troubleshooting Guide: SQL Server Error 5264 — DATA Page Count Mismatch

Why This Error Happens

Error 5264 occurs when DBCC CHECKDB detects a discrepancy between the number of data pages recorded in the system catalog (sys.systeminternalsallocation_units or similar metadata) and the actual number of pages physically allocated to that object in the database file. This typically stems from corruption in the allocation metadata caused by abrupt shutdowns, storage subsystem failures, faulty disk I/O, or bugs triggered during large bulk operations or improper index rebuilds. It can also surface after restoring a database from an inconsistent backup or following unexpected power loss on systems without proper write-caching safeguards.

Step-by-Step Resolution Guide

Step 1: Run a Full Consistency Check

• Open SQL Server Management Studio (SSMS) or use sqlcmd connected to the affected instance.
• Execute DBCC CHECKDB ('YourDatabaseName') WITH ALL_ERRORMSGS, NO_INFOMSGS; to capture the full scope of corruption, not just the single table referenced in the error.
• Record the exact object ID and table name reported alongside Error 5264 for targeted repair later.

Step 2: Check the Underlying Storage Health

• On Windows Server 2025, open Event Viewer (eventvwr.msc) and inspect the System log for Disk, NTFS, or Storage Spaces warnings (Event IDs 7, 51, or 153).
• Run chkdsk D:\ /f /r (replace D:\ with the actual data drive) in an elevated PowerShell/Command Prompt to scan for physical disk errors.
• If using Storage Spaces Direct or a SAN, verify the volume health via Get-VirtualDisk and Get-PhysicalDisk in PowerShell.

Step 3: Restore from a Known-Good Backup (Preferred Fix)

• Identify the last clean full backup taken before corruption was introduced, located typically in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup\.
• Restore the database to a new instance or a side-by-side location using RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\Backups\YourDatabaseName.bak' WITH REPLACE;
• Reapply any transaction log backups taken after the full backup to minimize data loss.

Step 4: Run Targeted Repair if No Backup Is Available

• As a last resort, execute DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) only after taking a full backup of the current (corrupted) state.
• Understand this command may delete corrupted pages or rows to resolve the allocation mismatch, resulting in permanent data loss for affected rows.
• Place the database in single-user mode first using ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE; before running the repair.

Alternative or Verification Steps

• After remediation, re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS; and confirm it returns zero allocation or consistency errors.
• If corruption recurs frequently, enable Windows Server 2025 Storage Replica or move the database files to verified, non-faulty storage, and review SQL Server error logs (ERRORLOG in the MSSQL\Log directory) for recurring I/O warnings (Event ID 823/824).

Troubleshooting Guide: SQL Server Error 5263 – Incorrect Row Count(s) for Table

Why This Error Happens

Error 5263 occurs when SQL Server 2025’s consistency checker (DBCC CHECKTABLE/CHECKDB) detects that the row counts stored in the internal metadata (sys.partitions or sys.dmdbpartition_stats) do not match the actual number of rows physically present in the table or index. This discrepancy is typically caused by corrupted non-clustered index pages, an improperly terminated transaction during a bulk insert/delete operation, or disk-level corruption from a sudden power loss or storage failure on the underlying NTFS/ReFS volume. In rarer cases, this is triggered by running SQL Server on Windows Server 2025 with a storage driver that doesn’t properly flush write caches, leading to torn pages.

Step-by-Step Resolution Guide

Step 1: Run a Full Diagnostic Check

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to the affected instance.
• Execute DBCC CHECKTABLE ('YourTableName') WITH ALL_ERRORMSGS, NO_INFOMSGS; to confirm the scope of the corruption.
• Review the output for additional associated errors (such as 8928 or 2533) that indicate physical page corruption alongside the count mismatch.

Step 2: Isolate and Backup the Database

• Immediately take a full backup of the database (even if corrupted) to preserve the current state before attempting repairs: BACKUP DATABASE [YourDB] TO DISK = 'C:\Backups\YourDB_PreRepair.bak';
• Place the database in SINGLE_USER mode to prevent further writes during the repair process: ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Step 3: Execute the Repair Command

• Run the targeted repair statement, as the count mismatch is usually resolved without data loss via: DBCC CHECKTABLE ('YourTableName', REPAIR_REBUILD);
• If the error persists specifically on an index, rebuild the non-clustered indexes manually using: ALTER INDEX ALL ON YourTableName REBUILD;
• Avoid using REPAIR_ALLOW_DATA_LOSS unless the REPAIR_REBUILD step fails, as it may delete inconsistent rows entirely.

Step 4: Restore Multi-User Access

• Once the repair commands complete successfully, return the database to multi-user mode: ALTER DATABASE [YourDB] SET MULTI_USER;
• Verify that application connectivity strings are correctly re-establishing sessions with the Windows Server 2025 instance.

Alternative or Verification Steps

• Re-run DBCC CHECKTABLE ('YourTableName') WITH NO_INFOMSGS; immediately after the repair; a clean return with 0 allocation errors and 0 consistency errors confirms the fix.
• If corruption recurs immediately after repair, check the Windows Event Viewer (System Logs) for underlying Disk or NTFS corruption warnings, as this suggests a hardware-level failure requiring a full restore from a clean backup rather than a logical repair.

Troubleshooting Guide: SQL Server Error 5262 – “Object ID %d”

Why This Error Happens

Error 5262 in Microsoft SQL Server 2025 typically occurs when the database engine encounters a corrupted or inconsistent page structure linked to a specific Object ID, often due to allocation metadata mismatches in system catalog views. This can result from improper shutdowns, storage-level corruption, or incomplete transactions during DDL operations that left orphaned allocation units. In some cases, it is triggered by antivirus or backup software locking database files during read/write operations, causing the storage engine to misread page headers.

Step-by-Step Resolution Guide

Step 1: Verify Database Integrity

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify the scope of corruption tied to the reported Object ID.
• Note the specific Object ID and associated index/table name returned in the output for cross-referencing.

Step 2: Isolate and Repair the Affected Object

• If DBCC CHECKDB confirms corruption, attempt a targeted repair using DBCC CHECKTABLE ('TableName') WITH REPAIR_REBUILD; on the specific object first (avoid REPAIR_ALLOW_DATA_LOSS unless necessary).
• Check the SQL Server error log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for correlating I/O warnings (Error 823 or 824) that may point to underlying disk issues.
• If disk-level issues are suspected, run chkdsk C: /f /r on the Windows Server 2025 volume hosting the .mdf/.ldf files (requires a reboot if the volume is in use).

Step 3: Restore from a Clean Backup (If Repair Fails)

• If repair commands fail or report unresolvable corruption, restore the database from the last known good backup using RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabaseName.bak' WITH REPLACE;.
• Ensure the SQL Server service account has read/write permissions on the backup directory (D:\Backups) via Windows Security tab properties.
• Apply transaction log backups sequentially if using a full recovery model to minimize data loss.

Alternative or Verification Steps

• After remediation, re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS; and confirm zero allocation or consistency errors are returned for the previously flagged Object ID.
• If the error recurs intermittently, check Windows Event Viewer (Windows Logs > System) for Disk or NTFS warnings, as this often indicates a failing physical drive rather than a SQL Server configuration issue.

Troubleshooting Guide: Error 5261 in Microsoft SQL Server 2025

Why This Error Happens

Error 5261 occurs when SQL Server’s online page-move operation (typically during an online index rebuild or shrink operation) encounters a page that has been allocated but never properly formatted with a valid page header. This usually indicates underlying database corruption, often caused by improper shutdowns, storage subsystem failures, or interrupted I/O operations that left allocation metadata out of sync with actual page contents. It can also surface when third-party backup or disk-imaging tools interact with live database files, leading to inconsistent page states.

Step-by-Step Resolution Guide

Step 1: Verify the Scope of Corruption

• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; from SQL Server Management Studio (SSMS) or sqlcmd.
• Review the output for allocation errors (Msg 2533, 8909, or similar) tied to the same page ID referenced in Error 5261.
• Document the affected PageId and FileId pair reported in the error message (e.g., Page 1:4521).

Step 2: Check Storage and I/O Subsystem Health

• On Windows Server 2025 / Windows 11, open Event Viewer and inspect the System log under Applications and Services Logs > Microsoft > Windows > Storage for Disk or NTFS warnings.
• Run chkdsk D: /f /r (replace D: with the actual drive hosting your .mdf/.ndf files) to scan for physical disk errors.
• Confirm there were no unexpected power-loss events or forced VM snapshots coinciding with the corruption timestamp.

Step 3: Halt the Operation Causing the Error

• Identify if the error was triggered by an online index rebuild or shrink command (DBCC SHRINKFILE or ALTER INDEX ... REBUILD WITH (ONLINE = ON)).
• Cancel the operation safely using KILL <session_id>; if it is still running.
• Switch to an offline rebuild (ONLINE = OFF) as a temporary workaround while corruption is being resolved.

Step 4: Repair the Database

• Place the database in single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Run DBCC CHECKDB (YourDatabaseName, REPAIR_ALLOW_DATA_LOSS); — note this may discard the corrupted page’s data.
• Return to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 5: Restore from a Known Good Backup (If Repair Fails)

• If REPAIR_ALLOW_DATA_LOSS cannot resolve the page, restore the affected file/filegroup from your last verified full backup.
• Use RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabase.bak' WITH REPLACE, RECOVERY;
• Apply any available transaction log backups to minimize data loss.

Alternative or Verification Steps

• Re-run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; after remediation — a clean result with zero allocation or consistency errors confirms the fix.
• If corruption recurs on the same page after repair, treat it as a hardware-level issue and run full diagnostics on the underlying storage array or SAN controller before re-attaching the database.

Troubleshooting Guide: SQL Server Error 5260 – “Object ID %d”

Why This Error Happens

Error 5260 in Microsoft SQL Server typically occurs when the database engine encounters an inconsistency between system catalog metadata and the actual object structure, often due to corruption in system tables or allocation pages. This can result from improper shutdowns, storage subsystem failures, or interrupted DDL operations that left the object’s metadata in SQL Server’s internal catalogs out of sync. In some cases, this error surfaces during DBCC CHECKDB operations or when the engine attempts to resolve an Object ID that no longer maps correctly to a valid schema entry.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Object and Database

• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; to capture the full error context.
• Note the exact Object ID referenced in the error (%d) using SELECT OBJECT_NAME(<ObjectID>); to resolve it to a table or index name.
• Check the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for additional stack trace details.

Step 2: Validate System Catalog Consistency

• Execute DBCC CHECKCATALOG (YourDatabaseName) WITH NO_INFOMSGS; to specifically target metadata corruption.
• Review results for orphaned or mismatched entries in sys.objects, sys.columns, or sys.indexes.
• If catalog corruption is confirmed, avoid making further schema changes to the database until repair is complete.

Step 3: Restore From a Known-Good Backup

• Navigate to your backup repository and identify the most recent valid full backup prior to the error onset.
• Restore the database to a test instance first using RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabaseName.bak' WITH REPLACE, RECOVERY;
• Validate the restored copy using Step 1 before promoting it to production.

Step 4: Run Repair Operations (If No Clean Backup Exists)

• Set the database to single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Run DBCC CHECKDB (YourDatabaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS; only as a last resort, as this may delete corrupted rows or objects.
• Revert to multi-user mode afterward: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 5: Check Underlying Storage Health

• On Windows Server 2025/Windows 11, open Event Viewer and inspect the System log under Windows Logs for Disk or NTFS warnings (Event IDs 7, 51, 153).
• Run chkdsk D: /f /r (replace D: with your SQL data drive) during a scheduled maintenance window to rule out physical disk corruption.

Alternative or Verification Steps

• Re-run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; after remediation to confirm zero allocation or consistency errors are returned.
• If Error 5260 persists after repair and restore attempts, engage Microsoft Support with the SQL Server error log and a current DBCC CHECKDB output, as this may indicate a deeper storage driver or hardware-level fault requiring vendor diagnostics.

Why This Error Happens

Error 5259 occurs when SQL Server attempts to move a heap page during an online operation (such as an index rebuild or ALTER TABLE) but fails to recompute the value of a computed column during the process. This typically stems from a corrupted or inconsistent computed column definition, a schema mismatch between the column’s expression and underlying data types, or an orphaned reference to a function/column that no longer exists. In some cases, underlying data corruption on the heap page itself prevents the expression evaluator from reading the source values needed for the computation.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Table and Computed Column

• Run DBCC CHECKTABLE('YourTableName') WITH ALL_ERRORMSGS, NO_INFOMSGS to confirm the specific heap page and table referenced in the error.
• Query sys.computed_columns to review the exact definition:

  SELECT name, definition, is_persisted FROM sys.computed_columns WHERE object_id = OBJECT_ID('YourTableName');

• Note any columns referenced in the computed expression that may have been altered, dropped, or renamed.

Step 2: Validate and Repair the Computed Column Expression

• Manually execute the computed column’s underlying expression as a standalone SELECT statement against the table to see if it throws an error (e.g., divide-by-zero, invalid cast, or missing scalar function).
• If the expression references a user-defined function (UDF), verify the function still exists and compiles correctly using sys.objects.
• If corruption is suspected rather than a logic error, run:

  DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• If the column is marked PERSISTED, consider dropping and recreating it with corrected logic:

  ALTER TABLE YourTableName DROP COLUMN ComputedColName;
  ALTER TABLE YourTableName ADD ComputedColName AS (CorrectedExpression) PERSISTED;

Step 3: Rebuild the Heap and Clear Fragmentation

• Once the expression is fixed, rebuild the heap to force page reorganization:

  ALTER TABLE YourTableName REBUILD;

• If the table has no clustered index, use ALTER TABLE ... SET (LOCK_ESCALATION = TABLE) combined with a dummy clustered index creation/drop cycle to force a full heap rewrite.
• Monitor SQL Server Error Log (via sp_readerrorlog or Event Viewer under Applications and Services Logs > Microsoft > SQL Server) to confirm Error 5259 no longer appears during the operation.

Step 4: Restore From Backup If Corruption Persists

• If DBCC CHECKDB reports allocation or consistency errors tied to the same page, isolate the table and restore it from the most recent clean backup using RESTORE DATABASE ... WITH PAGE = 'FileID:PageID' for page-level restore (Enterprise edition only).
• For non-Enterprise editions, perform a full database restore in a side environment and re-import the affected table’s data via INSERT INTO ... SELECT into the production table.

Alternative or Verification Steps

• Re-run DBCC CHECKTABLE('YourTableName') WITH ALL_ERRORMSGS, NO_INFOMSGS after remediation — a clean result with zero allocation errors confirms the fix.
• As an alternative if rebuilding fails, export the table schema and data via bcp or SSMS Generate Scripts, drop and recreate the table with a corrected computed column definition, then reimport the data to guarantee a clean heap structure.

Troubleshooting Guide: SQL Server Error 5258

Why This Error Happens

Error 5258 occurs when SQL Server’s internal page defragmentation or heap rebuild operation attempts to relocate a data page but fails while recalculating the value of a computed column during the move. This is typically caused by corruption in the underlying expression metadata, a schema mismatch between the computed column definition and the data types of its dependent columns, or corrupted system catalog entries referencing the computed column. It can also surface when a user-defined function (UDF) referenced in the computed column expression has become invalid, inaccessible, or was altered/dropped without properly updating dependent objects.

Step-by-Step Resolution Guide

Step 1: Run a Full Consistency Check on the Database

• Open SQL Server Management Studio (SSMS) or connect via sqlcmd on Windows Server 2025.
• Execute: DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Review output specifically for corruption flags related to the heap table or computed column catalog entries (look for errors near Msg 5258 or related Msg 8952/2534).
• Note the exact table and computed column name reported in the error output.

Step 2: Validate the Computed Column Expression and Dependencies

• Query system catalogs to inspect the computed column definition:
  SELECT name, definition, is_persisted FROM sys.computed_columns WHERE object_id = OBJECT_ID('YourTableName');
• Check if the expression references a scalar UDF; if so, verify the function still exists and compiles:
  SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.YourFunctionName'));
• If the function is missing or broken, recreate it using CREATE OR ALTER FUNCTION with the original logic restored from source control or backup scripts.

Step 3: Rebuild the Heap Table to Force Recalculation

• Since heaps lack clustered indexes, trigger a rebuild using:
  ALTER TABLE YourTableName REBUILD;
• If this fails again with Error 5258, temporarily drop and recreate the computed column:
  ALTER TABLE YourTableName DROP COLUMN YourComputedColumn;
ALTER TABLE YourTableName ADD YourComputedColumn AS (your_expression_here);
• Ensure the expression syntax matches exactly, including data type casting, to avoid re-triggering the corruption.

Step 4: Restore from Backup if Corruption Persists

• If DBCC CHECKDB reports unrepairable corruption in the page specified (e.g., page ID matching the %d:%d in the error), isolate the affected table.
• Restore the specific filegroup or full database from the last known good backup using:
  RESTORE DATABASE YourDatabaseName FROM DISK = 'E:\Backups\LastGood.bak' WITH REPLACE, RECOVERY;
• Re-apply any transaction log backups taken after the base restore point to minimize data loss.

Alternative or Verification Steps

• Run DBCC CHECKDB a second time after remediation to confirm zero allocation or consistency errors are returned.
• If the error reoccurs only during index maintenance jobs, isolate the issue by running ALTER TABLE ... REBUILD manually during a maintenance window rather than relying on the automated SQL Server Agent job, then inspect the Windows Event Viewer (Application Log) for correlating Event ID 5258 timestamps.

Troubleshooting Guide: SQL Server Error 5257 – File Skipped During Shrink Operation

Why This Error Happens

This error occurs when a concurrent process (such as autogrowth, another shrink operation, or an administrator action) changes the size of a database file while a DBCC SHRINKFILE or DBCC SHRINKDATABASE operation is actively running against it. SQL Server 2025 detects this race condition and intentionally skips the file to prevent internal metadata corruption, rather than risk an inconsistent file state. This is typically not a sign of corruption itself, but rather a symptom of overlapping maintenance jobs, aggressive autogrowth settings, or manual intervention conflicting with a scheduled shrink task.

Step-by-Step Resolution Guide

Step 1: Identify Concurrent Operations

• Open SQL Server Management Studio (SSMS) and query sys.dm_exec_requests to check for other active sessions running DBCC SHRINKFILE, DBCC SHRINKDATABASE, or ALTER DATABASE MODIFY FILE commands against the same database ID.
• Check SQL Server Agent Job History (via msdb.dbo.sysjobhistory) for overlapping maintenance plans, specifically “Shrink Database” tasks scheduled too close together.
• Cross-reference the specific File ID and Database ID mentioned in the error against sys.master_files to identify the exact logical file name.

Step 2: Disable Autogrowth Conflicts

• Autogrowth events triggered mid-shrink are the most common cause of this specific error. Review the file growth settings via SSMS: Right-click Database > Properties > Files.
• Ensure “Autogrowth/Maxsize” is set to a fixed MB value rather than a percentage, as percentage-based growth calculations can spike resource contention during shrink windows.
• Temporarily pause heavy transactional workloads or batch jobs during the shrink window to prevent the log or data file from needing to grow while it is being shrunk.

Step 3: Reschedule and Isolate the Shrink Job

• Edit the SQL Server Agent Job or Maintenance Plan responsible for the shrink operation.
• Ensure no other Index Rebuild or Reorganize jobs are running in parallel, as these operations consume space and force file growth, directly conflicting with the shrink thread.
• Move the shrink task to a dedicated maintenance window where no other write-heavy transactions are expected.

Step 4: Manually Re-run the Shrink Operation

• Once conflicting jobs are isolated, manually trigger the specific shrink command using T-SQL to confirm the fix:

USE [YourDatabaseName];
GO
DBCC SHRINKFILE (N'YourLogicalFileName', 0, TRUNCATEONLY);
GO

• Monitor the “Messages” tab in SSMS for the completion status to ensure the File ID is no longer being skipped.

Alternative or Verification Steps

• Verification: Check the SQL Server Error Log (via EXEC sp_readerrorlog) immediately after the job runs again; if Error 5257 does not reappear and the file size reflects the expected reduction in sys.database_files, the fix was successful.
• Alternative: If the issue persists due to high-frequency autogrowth in a high-transaction environment, consider disabling the shrink task entirely for that specific database and rely on proper capacity planning, as frequent shrinking on active OLTP databases often causes fragmentation and performance degradation regardless of this specific error.

Troubleshooting Guide: SQL Server Error 5256 – Table Error in Allocation Unit

Why This Error Happens

Error 5256 occurs when SQL Server’s consistency checker (DBCC CHECKDB) detects that the page allocation metadata for a specific allocation unit does not match the actual data pages, indicating physical corruption at the storage level. This typically results from unexpected I/O subsystem failures, improper shutdowns, faulty disk controllers, or storage-level bit rot affecting the database files (.mdf/.ndf). In Windows Server 2025 environments, this can also be triggered by misconfigured storage drivers or incomplete writes caused by abrupt VM or container restarts in virtualized deployments.

Step-by-Step Resolution Guide

Step 1: Isolate the Database and Confirm Corruption Scope

• Place the affected database in single-user or restricted mode to prevent further writes: ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Run a full consistency check to identify the exact scope: DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Note the specific object ID and allocation unit ID referenced in the error output for targeted repair.

Step 2: Attempt Repair Using the Least Destructive Option

• Run DBCC CHECKDB ('YourDB', REPAIR_REBUILD) first, as this option fixes most allocation errors without data loss.
• Avoid REPAIR_ALLOW_DATA_LOSS unless Step 2’s rebuild option fails, since it may delete corrupted rows entirely.
• Monitor the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG for additional I/O warnings during the repair.

Step 3: Validate Underlying Hardware and Restore from Backup if Needed

• Check Windows Event Viewer under Windows Logs > System for Event ID 7, 9, or 11 (disk/controller errors) that may indicate failing physical storage.
• If corruption persists after repair, restore the database from the last known good backup using RESTORE DATABASE [YourDB] FROM DISK = 'E:\Backups\YourDB.bak' WITH REPLACE;
• Run chkdsk E: /f /r (replace with your actual SQL data drive letter) during a maintenance window to scan for underlying disk sector issues.

Alternative or Verification Steps

• After repair or restore, re-run DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS to confirm zero allocation errors are returned, then set the database back to multi-user mode: ALTER DATABASE [YourDB] SET MULTI_USER;
• If corruption recurs on the same allocation unit repeatedly, migrate the database files to a different physical disk or storage array, as this strongly indicates a failing hardware component rather than a logical SQL Server issue.

Troubleshooting Guide: SQL Server Error 5255 – Sort Page Movement Failure

Why This Error Happens

Error 5255 occurs when SQL Server’s internal page-allocation engine attempts to relocate a page that is currently flagged as part of an active sort operation, typically during index rebuilds, DBCC operations, or shrink/defrag processes. This condition is usually triggered by database corruption, an interrupted sort workspace in tempdb, or a concurrent maintenance operation (such as a shrink running simultaneously with an index rebuild) that conflicts over page ownership. In most cases, the underlying cause is either disk-level corruption or insufficient tempdb space causing improper sort page management.

Step-by-Step Resolution Guide

Step 1: Verify Database and Tempdb Integrity

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify corruption.
• Check tempdb health separately using DBCC CHECKDB (tempdb); since sort operations rely heavily on tempdb workspace.
• Review the SQL Server error log (via C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for related I/O or allocation warnings preceding Event ID 5255.

Step 2: Halt Conflicting Maintenance Operations

• Confirm no concurrent DBCC SHRINKDATABASE, DBCC SHRINKFILE, or index reorganize/rebuild jobs are running against the same database simultaneously.
• Query sys.dm_exec_requests to identify any active sessions performing sort-dependent operations.
• Terminate or reschedule overlapping jobs via SQL Server Agent (Object Explorer > SQL Server Agent > Jobs).

Step 3: Resize and Validate Tempdb Configuration

• Navigate to SQL Server Management Studio (SSMS) > Server Properties > Database Settings and confirm tempdb data/log file growth settings are not set to restrictive fixed sizes.
• Increase tempdb initial size using: ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 8192MB);
• Ensure tempdb files are placed on a dedicated, low-latency SSD volume (e.g., E:\TempDB\) separate from OS and user database drives.

Step 4: Rebuild Affected Indexes Offline

• Identify the affected object using the page ID reported in the error (Page %d:%d) via DBCC PAGE (with trace flag 3604 enabled) to map it to a table/index.
• Rebuild the specific index offline to avoid concurrent sort conflicts: ALTER INDEX [IndexName] ON [TableName] REBUILD WITH (ONLINE = OFF, SORT_IN_TEMPDB = ON);
• Monitor Windows Event Viewer (Applications and Services Logs > MSSQLSERVER) for recurrence during the rebuild.

Step 5: Apply Latest Cumulative Update

• Confirm your SQL Server 2025 build number via SELECT @@VERSION; and compare against the latest Cumulative Update (CU) from Microsoft’s release notes.
• Download and install the newest CU package, as sort-page handling bugs in allocation logic are frequently addressed in servicing updates.
• Restart the SQL Server service through Services.msc (Windows Server 2025) after installation.

Alternative or Verification Steps

• Re-run DBCC CHECKDB and the previously failing maintenance operation (index rebuild/shrink) to confirm Error 5255 no longer appears in the SQL Server logs or Windows Event Viewer.
• If the error persists, restore the affected database from a known-good backup to a test instance and compare index/page structures to isolate whether the corruption is storage-hardware related (check Windows Server 2025 Disk Management and run chkdsk on the underlying volume).

Troubleshooting Guide: SQL Server Error 5254 — Heap Page Move Conflict

Why This Error Happens

Error 5254 occurs when a concurrent process (such as an online index rebuild, DBCC DBREINDEX operation, or another heap maintenance task) is already in the process of building or reorganizing a heap, and a second operation attempts to move a page within that same heap simultaneously. This is typically a concurrency conflict rather than actual data corruption, often triggered when multiple maintenance jobs (e.g., index rebuilds and statistics updates) overlap on the same table. It can also surface when a previous heap rebuild operation was interrupted abnormally, leaving internal metadata in a transitional state that blocks subsequent heap operations.

Step-by-Step Resolution Guide

Step 1: Identify Conflicting Sessions and Operations

• Run sp_who2 or query sys.dm_exec_requests to identify any active sessions performing index rebuilds or heap maintenance on the affected table.
• Use the following query to check for blocking or long-running heap operations:

  SELECT session_id, status, command, blocking_session_id, wait_type, wait_time
  FROM sys.dm_exec_requests
  WHERE command LIKE '%INDEX%' OR command LIKE '%HEAP%';

• Note the object_id and session_id of any conflicting process before proceeding.

Step 2: Terminate or Wait Out the Conflicting Process

• If an index rebuild or heap reorganization is confirmed to be stuck or abandoned, safely cancel it using KILL <session_id>.
• If the operation is legitimate and actively progressing, allow it to complete fully before retrying your original command.
• Avoid running ALTER TABLE ... REBUILD and DBCC DBREINDEX concurrently on the same heap table from different jobs or scripts.

Step 3: Rebuild the Heap Cleanly

• Once all conflicting sessions are cleared, rebuild the heap using a single, isolated operation:

  ALTER TABLE [YourSchema].[YourTable] REBUILD;

• Ensure no SQL Agent jobs, maintenance plans, or third-party backup tools are scheduled to run concurrently against the same table.
• Check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for any related entries confirming the rebuild completed without further 5254 errors.

Step 4: Run Consistency Checks

• Execute DBCC CHECKTABLE('YourTable') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm there is no underlying physical corruption triggering repeated conflicts.
• If corruption is detected, follow standard DBCC CHECKDB repair procedures using a verified backup first.

Alternative or Verification Steps

• Re-run the original maintenance task (index rebuild, heap reorganize, or DBCC command) in isolation to confirm Error 5254 no longer appears in the SQL Server Error Log or Windows Event Viewer (Event ID 5254 under Application logs).
• If the error persists, schedule maintenance jobs with explicit sequencing (using SQL Agent job step dependencies) to guarantee no overlapping heap operations occur on the same table in the future.

Troubleshooting Guide: Error 5253 — DBCC CHECKALLOC Cannot Be Run on tempdb

Why This Error Happens

This error occurs because DBCC CHECKALLOC has specific restrictions when run against tempdb due to how SQL Server manages allocation structures in the temporary database. Unlike user databases, tempdb is recreated at every service restart and uses a simplified allocation model that does not support the full consistency check logic used by CHECKALLOC. In most cases, this error surfaces because a maintenance script, third-party monitoring tool, or integrity-check job was written generically to loop through all databases—including tempdb—without excluding it, or because the script is attempting to run DBCC CHECKALLOC with options (like ESTIMATEONLY combined with repair clauses) that are invalid for tempdb‘s architecture.

Step-by-Step Resolution Guide

Step 1: Identify the Source of the DBCC Call

• Check SQL Server Agent job history for the job that triggered the error (sysjobhistory or SSMS Job Activity Monitor).
• Review any custom maintenance scripts, especially ones using sp_MSforeachdb or sys.databases loops.
• Look for third-party backup/integrity tools (e.g., Ola Hallengren’s scripts, SQL Sentry, Redgate) configured to run checks on “all databases.”

Step 2: Exclude tempdb from the Integrity Check Script

• If using Ola Hallengren’s DatabaseIntegrityCheck.sql, set the @Databases parameter to exclude system databases where not applicable:

  EXECUTE dbo.DatabaseIntegrityCheck
      @Databases = 'USER_DATABASES',
      @CheckCommands = 'CHECKDB'

• If using a custom script with sp_MSforeachdb, add a filter:

  EXEC sp_MSforeachdb '
  IF ''?'' NOT IN (''tempdb'')
  BEGIN
      DBCC CHECKALLOC(''?'') WITH NO_INFOMSGS
  END'

• Save and re-deploy the modified script to the SQL Server Agent job step.

Step 3: Use DBCC CHECKDB Instead for tempdb (If Validation Is Required)

• tempdb cannot be checked with CHECKALLOC alone, but DBCC CHECKDB can still be run against it on SQL Server 2025 for structural validation:

  DBCC CHECKDB (tempdb) WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Note: CHECKDB on tempdb has limitations too (no repair options), but it will not throw Error 5253.
• Schedule this as a separate job step from your general database integrity checks.

Step 4: Restart the SQL Server Service to Reset tempdb (Optional)

• Open Services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER) or your named instance.
• Right-click and select Restart — this forces tempdb to be rebuilt fresh, clearing any transient allocation anomalies that may have prompted the check.
• Confirm the restart completed via SQL Server Error Log (EXEC sp_readerrorlog 0, 1, 'tempdb').

Alternative or Verification Steps

• Re-run the modified maintenance job manually in SQL Server Agent and confirm the job history shows Success with no Error 5253 in the output log.
• As an alternative verification, run DBCC CHECKALLOC manually against a user database only (e.g., DBCC CHECKALLOC('YourDB') WITH NO_INFOMSGS;) to confirm the command itself works correctly outside of tempdb, proving the exclusion logic resolved the issue.

Troubleshooting Guide: SQL Server Error 5252 – Shrink Operation Failure Due to Deadlocks

Why This Error Happens

Error 5252 occurs when the DBCC SHRINKFILE or DBCC SHRINKDATABASE operation cannot acquire the necessary locks on data pages because other active transactions are competing for the same resources. This is common in high-concurrency OLTP environments where frequent inserts, updates, and deletes create lock contention during the page-compaction phase of the shrink process. Since shrink operations require exclusive access to move pages toward the beginning of the file, any concurrent write activity on the affected file increases the likelihood of deadlocks, causing SQL Server to abort the operation before reaching the target size.

Step-by-Step Resolution Guide

Step 1: Identify Low-Activity Windows and Current Blocking Sessions

• Query sys.dm_tran_locks and sys.dm_exec_requests to identify sessions currently holding locks on the target database.
• Run SELECT * FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE 'LCK%' to confirm active lock contention.
• Use SQL Server Agent or Windows Task Scheduler to review peak usage hours and plan the shrink during off-peak periods (e.g., maintenance windows).

Step 2: Re-run the Shrink Operation with Throttling

• Instead of a full shrink, use incremental shrinking with a smaller target size to reduce the duration of lock holds:

DBCC SHRINKFILE (N'YourFileLogicalName', TargetSizeMB);

• Add the NOTRUNCATE option first to compact pages without releasing space immediately, then follow with a TRUNCATEONLY pass during a quieter period:

DBCC SHRINKFILE (N'YourFileLogicalName', NOTRUNCATE);
DBCC SHRINKFILE (N'YourFileLogicalName', TRUNCATEONLY);

• This reduces the duration each shrink pass needs exclusive access, lowering deadlock probability.

Step 3: Adjust Lock Timeout and Isolation Settings

• Increase LOCK_TIMEOUT for the session running the shrink to prevent premature failure, but balance this against blocking other users:

SET LOCK_TIMEOUT 5000;

• Consider enabling Read Committed Snapshot Isolation (RCSI) on the database to reduce reader/writer blocking during shrink:

ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;

• Restart the shrink operation after confirming RCSI is active and workload has decreased.

Step 4: Schedule Automated Retry Logic

• Create a SQL Server Agent job that monitors active transactions and automatically retries the shrink operation when concurrent session count drops below a defined threshold (e.g., fewer than 10 active write transactions).
• Use sp_who2 or Extended Events to log concurrency levels before each retry attempt.
• Set the job to retry up to 3 times with a 15-minute interval between attempts.

Alternative or Verification Steps

• Verify the fix by checking the file size post-shrink using sys.database_files and confirming size matches the expected target in 8KB pages.
• If deadlocks persist even during low-traffic windows, consider rebuilding indexes with ONLINE = ON first to reduce fragmentation, which can make the shrink operation faster and less lock-intensive overall.

Troubleshooting Guide: Error 5251 – Heap Page Move Failure Due to NC Index Maintenance

Why This Error Happens

Error 5251 occurs when SQL Server 2025 attempts to relocate a heap page during an online operation (such as an online heap rebuild or data compression change) but fails to synchronize the associated nonclustered (NC) indexes with the new row locations. This typically stems from corrupted index metadata, insufficient tempdb space to stage the NC index updates, or a concurrency conflict where another transaction is modifying the same heap pages during the move. In some cases, this also surfaces due to underlying disk I/O corruption affecting the index allocation map (IAM) chains that link the heap to its NC indexes.

Step-by-Step Resolution Guide

Step 1: Verify Database and Index Consistency

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to detect page-level or allocation corruption.
• Specifically check NC indexes tied to the heap using DBCC CHECKTABLE ('YourTableName') WITH ALL_ERRORMSGS;.
• Review the SQL Server Error Log (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for accompanying corruption messages (e.g., Error 824 or 825).

Step 2: Check and Expand Tempdb Capacity

• Online heap maintenance operations heavily rely on tempdb for sorting and staging NC index updates.
• Navigate to SQL Server Management Studio (SSMS) > Databases > tempdb > Properties > Files and ensure autogrowth is not restricted.
• Alternatively, run the following T-SQL to check current allocation:

  EXEC sp_helpdb 'tempdb';

• Increase the initial size of tempdb data files if they are hitting capacity limits during the operation.

Step 3: Rebuild the Heap and NC Indexes Offline

• If the online move continues to fail, disable the ONLINE = ON option and force an offline rebuild to bypass the concurrent maintenance conflict.
• Execute the following command, replacing placeholders with your actual table name:

  ALTER TABLE YourTableName REBUILD WITH (ONLINE = OFF);

• This forces SQL Server to take a schema modification lock, ensuring no other process interferes with the NC index synchronization.

Step 4: Update SQL Server 2025 Cumulative Updates

• Ensure your instance is patched to the latest Cumulative Update (CU) for SQL Server 2025, as early releases may contain bugs related to heap page allocation logic in Windows Server 2025 environments.
• Check your current build version by running SELECT @@VERSION;.
• Download and apply the latest CU from the Microsoft Update Catalog if your build number is outdated.

Alternative or Verification Steps

• Verification: After the rebuild, run DBCC CHECKTABLE again on the affected table to confirm a 0-error result, and check the Error Log to ensure Error 5251 is no longer logged during subsequent maintenance jobs.
• Alternative Fix: If the error persists on specific tables, consider converting the heap to a Clustered Index (if business logic permits), as this eliminates the heap-to-NC-index pointer maintenance that triggers this specific error.

Troubleshooting Guide: SQL Server Error 5250 – Invalid Page / Corruption Requiring Restore

Why This Error Happens

Error 5250 occurs when the SQL Server Storage Engine detects that a database page has a corrupted header, checksum mismatch, or torn write signature that falls outside the scope of automatic repair (DBCC CHECKDB REPAIRALLOWDATA_LOSS cannot fix it). This typically results from underlying disk/storage subsystem failures, unexpected power loss during a write operation, faulty RAID controllers, or bugs in third-party I/O filter drivers (antivirus/backup agents) intercepting SQL Server’s I/O requests on Windows Server 2025. Because the page structure itself is unreadable or logically inconsistent at a level SQL Server cannot reconstruct from transaction logs, the engine flags it as unrepairable and mandates a restore from a known-good backup.

Step-by-Step Resolution Guide

Step 1: Isolate the Database and Stop Further Writes

• Immediately set the database to read-only or single-user mode to prevent additional corruption: ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Avoid running REPAIR_ALLOW_DATA_LOSS yet — gather diagnostic data first.
• Check SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact Page ID (%S_PGID) and File ID referenced in the error.

Step 2: Run Full Consistency Check

• Execute DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY; to scope the full extent of corruption.
• Review output for additional allocation or B-tree errors beyond the single reported page.
• Cross-reference the Windows Event Viewer (System log) for underlying disk warnings (Event ID 7, 9, 11 from the disk or Storport source), which often confirm hardware-level root cause.

Step 3: Restore the Affected Page or Database from Backup

• If you have TRUSTWORTHY backups and the database is in FULL recovery model, attempt a page-level restore (fastest, minimal downtime):
  RESTORE DATABASE [YourDB] PAGE='FileID:PageID' FROM DISK = 'E:\Backups\YourDB_Full.bak' WITH NORECOVERY;
  followed by applying subsequent log backups and WITH RECOVERY.
• If page restore isn’t viable (e.g., SIMPLE recovery model or no log chain), perform a full database restore:
  RESTORE DATABASE [YourDB] FROM DISK = 'E:\Backups\YourDB_Full.bak' WITH REPLACE, RECOVERY;
• Ensure the backup file predates the corruption event by checking backup history in msdb.dbo.backupset.

Step 4: Address the Root Hardware/Storage Cause

• Run chkdsk E: /f /r (adjust drive letter) on the Windows Server 2025 volume hosting the .mdf/.ndf files to scan for physical sector issues.
• Update Storport/RAID controller drivers via Windows Update or vendor (Dell/HPE) utility, and check firmware on NVMe/SSD arrays.
• Temporarily exclude SQL Server data/log directories from antivirus real-time scanning to rule out I/O filter interference.

Alternative or Verification Steps

• After recovery, re-run DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; and confirm zero allocation/consistency errors before returning the database to MULTI_USER mode.
• If no valid backup exists, as a last resort, engage Microsoft Customer Support Services or use DBCC CHECKDB ('YourDB', REPAIR_ALLOW_DATA_LOSS) only after taking a file-level copy of the corrupted database, understanding this will delete the unreadable page’s data permanently.

Troubleshooting Guide: Error 5249 – Page Could Not Be Moved Because Shrink Could Not Lock the Page

Why This Error Happens

This error occurs when SQL Server’s DBCC SHRINKFILE or DBCC SHRINKDATABASE operation attempts to relocate a page during the shrink process, but another session is holding a lock on that page at the same moment. This is typically caused by concurrent transactions, active queries, or replication/CDC processes accessing the same data pages being targeted for deallocation. It can also surface more frequently under heavy OLTP workloads where shrink operations compete with normal transactional activity for page-level locks.

Step-by-Step Resolution Guide

Step 1: Identify Blocking Sessions and Active Transactions

• Open SQL Server Management Studio (SSMS) and run sp_who2 or query sys.dm_exec_requests to identify sessions holding locks on the target database.
• Check sys.dm_tran_locks filtered by resource_database_id to pinpoint which session IDs (SPIDs) are locking pages in the file being shrunk.
• Note down any long-running transactions, open CDC/replication jobs, or index maintenance tasks running concurrently.

Step 2: Pause Conflicting Operations Before Retrying Shrink

• Temporarily disable or pause SQL Server Agent jobs, replication, or Change Data Capture (CDC) processes that may be touching the same tables.
• Navigate to SQL Server Agent > Jobs in SSMS and disable any jobs scheduled to run during the shrink window.
• Ensure no index rebuild/reorganize operations are active by checking sys.dm_exec_requests for command = 'DBCC' or 'ALTER INDEX'.

Step 3: Run Shrink During a Low-Activity Window

• Schedule the shrink operation during off-peak hours using Windows Task Scheduler or SQL Server Agent to minimize lock contention.
• Use a smaller, incremental shrink approach instead of a single large operation:

  DBCC SHRINKFILE (N'YourDataFile', 1024);

• Repeat the command in small batches rather than shrinking the entire file at once, allowing SQL Server to retry page moves with less contention.

Step 4: Rebuild Indexes After Shrink to Reduce Fragmentation Conflicts

• Since shrink operations can cause significant fragmentation, run an index rebuild immediately after the shrink completes.
• Use the following command, replacing with your actual table/index name:

  ALTER INDEX ALL ON YourTableName REBUILD;

• This reduces the chance of repeated page-lock conflicts on subsequent shrink attempts.

Step 5: Verify Database and File Health

• Run DBCC CHECKDB to ensure no underlying corruption is contributing to abnormal locking behavior.
• Confirm the database is not in an unexpected state (e.g., SINGLE_USER or EMERGENCY) using:

  SELECT state_desc FROM sys.databases WHERE name = 'YourDatabaseName';

Alternative or Verification Steps

• Re-run DBCC SHRINKFILE after completing the steps above and confirm no Error 5249 messages appear in the SQL Server Error Log or Windows Event Viewer (Event ID 5249).
• If the issue persists, consider setting the database to SINGLE_USER mode temporarily (during a maintenance window) to eliminate all concurrent access before retrying the shrink operation.

Troubleshooting Guide: SQL Server Error 5248

Why This Error Happens

Error 5248 is actually an informational message, not a failure—it is logged by DBCC CHECKDB when running with a repair option (such as REPAIR_REBUILD or REPAIR_ALLOW_DATA_LOSS) and confirms that a specific row in an index was successfully repaired. This message typically appears after corruption was detected in the underlying data pages, often caused by disk-level issues, improper shutdowns, storage driver faults, or bit-rot on the physical media. While the message itself indicates success, its presence means prior corruption existed and the root storage/hardware cause must still be investigated to prevent recurrence.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of the Repair

• Open the SQL Server Error Log via SSMS (Management > SQL Server Logs) or check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Search for all occurrences of Error 5248 and note the associated index name, table, and database.
• Cross-reference with the DBCC CHECKDB output to confirm whether repairs are fully complete or still in progress.

Step 2: Run a Full Consistency Check to Validate Database Health

• Open a query window and run:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Ensure no further allocation or consistency errors are returned.
• If additional corruption is found, re-run repair in single-user mode:

  ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);
  ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 3: Investigate the Underlying Storage Layer

• Check Windows Event Viewer under Windows Logs > System for Disk, NTFS, or Storport errors around the time corruption was first detected.
• On Windows Server 2025 / Windows 11, run Get-PhysicalDisk | Get-StorageReliabilityCounter in PowerShell to check for read/write/latency errors on the drive hosting the .mdf/.ndf files.
• If using virtualized storage (Hyper-V, SAN, or cloud-attached disks), verify there were no recent host-level storage failovers or snapshot interruptions.

Step 4: Restore From a Clean Backup if Data Integrity Is Uncertain

• If REPAIR_ALLOW_DATA_LOSS was used, assume some data may have been deleted during repair.
• Restore the affected table/database from the last known-good full backup plus transaction log backups to recover any lost rows:

  RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabaseName.bak' WITH NORECOVERY;
  RESTORE LOG YourDatabaseName FROM DISK = 'D:\Backups\YourDatabaseName_log.trn' WITH RECOVERY;

Step 5: Enable Proactive Corruption Monitoring

• Configure a recurring SQL Agent job to run DBCC CHECKDB weekly during low-traffic windows.
• Enable Page Verify CHECKSUM on the database to catch corruption earlier:

  ALTER DATABASE YourDatabaseName SET PAGE_VERIFY CHECKSUM;

Alternative or Verification Steps

• Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; and confirm it returns zero allocation and consistency errors.
• If corruption recurs on the same index/table after repair, consider dropping and rebuilding the index entirely (DROP INDEX + CREATE INDEX) rather than relying on DBCC repair, and run hardware diagnostics (chkdsk /f /r or vendor disk utilities) on the underlying volume.

Why This Error Happens

Error 5247 occurs during a DBCC CHECKDB repair operation when SQL Server 2025 detects that a nonclustered (secondary) index is missing a row that exists in the underlying base (clustered or heap) table. This mismatch is typically caused by page-level corruption from storage failures, improper shutdowns, or disk I/O errors that prevented the index B-tree from staying in sync with the base table. It can also surface after bit-flip corruption on SAN/NAS storage or when antivirus/backup software locks database files mid-write.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of Corruption

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; from SQL Server Management Studio (SSMS) or sqlcmd.
• Review the output for the specific object ID and index ID reported alongside Error 5247.
• Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating I/O warnings (Event ID 823, 824, or 825).

Step 2: Take a Full Backup Before Repair

• Execute BACKUP DATABASE [YourDatabaseName] TO DISK = 'D:\Backups\YourDatabaseName_PreRepair.bak' WITH INIT, COMPRESSION;
• This protects against data loss if the repair operation needs to be reversed or re-run.
• Verify there is sufficient disk space on the target backup volume (minimum 1.5x database size recommended).

Step 3: Run Targeted Repair with Data Loss Allowance

• Place the database in single-user mode: ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Execute DBCC CHECKDB ('YourDatabaseName', REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS; first, since this error is often resolved by rebuilding the index rather than deleting data.
• If REPAIR_REBUILD does not resolve it, escalate to DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) — note this may remove orphaned index entries that cannot be reconciled with the base table.
• Return the database to multi-user mode: ALTER DATABASE [YourDatabaseName] SET MULTI_USER;

Step 4: Rebuild Affected Indexes Manually (Preferred Alternative)

• Instead of relying solely on automated repair, manually drop and recreate the specific nonclustered index identified in Step 1 using DROP INDEX followed by CREATE INDEX ... WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
• This avoids potential data loss from REPAIR_ALLOW_DATA_LOSS since the index is simply rebuilt from current base table data.

Step 5: Investigate Underlying Hardware/Storage Issues

• Check Windows Server 2025 Event Viewer → Windows Logs → System for Disk or NTFS warnings correlating with the corruption timestamp.
• Run chkdsk D: /f /r on the volume hosting the .mdf/.ndf files during a maintenance window to rule out underlying file system issues.

Alternative or Verification Steps

• After remediation, re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; and confirm it returns zero allocation and consistency errors.
• If corruption recurs on the same index, restore from the pre-repair backup and failover to a secondary replica (Always On Availability Group) if configured, then investigate the storage subsystem for persistent hardware faults.

Troubleshooting Guide: Error 5246 – Repair Operations Cannot Be Performed on MSSQLSYSTEMRESOURCE Database

Why This Error Happens

This error occurs when DBCC CHECKDB (or a similar repair command) is executed with a REPAIR_ALLOW_DATA_LOSS or REPAIR_REBUILD option directly against the mssqlsystemresource (Resource) database. SQL Server intentionally blocks repair operations on this system database because it is a read-only database containing all system objects and is designed to be replaced via reinstallation or file-copy restoration rather than repaired. The underlying cause is typically a corrupted mssqlsystemresource.mdf/.ldf file, or an administrator unknowingly targeting the resource database ID (typically DB ID 32767) while troubleshooting corruption.

Step-by-Step Resolution Guide

Step 1: Confirm the Corruption Scope and Target Database

• Run SELECT name, database_id FROM sys.databases; to verify you are not mistakenly pointing repair commands at mssqlsystemresource.
• Check the SQL Server Error Log (via EXEC sp_readerrorlog; or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for messages confirming resource database corruption.
• Note the exact SQL Server version and build number using SELECT @@VERSION; — this is critical for Step 2.

Step 2: Replace the Resource Database Files from a Matching Build

• Stop the SQL Server service via services.msc or run NET STOP MSSQLSERVER in an elevated Command Prompt.
• Locate a clean copy of mssqlsystemresource.mdf and mssqlsystemresource.ldf matching your exact SQL Server 2025 build (from installation media or a verified backup) typically found at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\Templates\.
• Copy these template files over the corrupted ones located in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Restart the SQL Server service with NET START MSSQLSERVER.

Step 3: Apply the Latest Cumulative Update (CU)

• If a build mismatch occurs after replacing files (Resource DB version must match the Database Engine version exactly), download and apply the matching Cumulative Update for SQL Server 2025 from the Microsoft Update Catalog.
• Run the CU installer with elevated privileges and allow it to complete a full repair-install cycle, which resynchronizes the resource database version with the engine.

Step 4: Rebuild System Databases if Replacement Fails

• If file replacement does not resolve the corruption, run setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=BUILTIN\Administrators /SAPWD=<StrongPassword> from the installation media.
• This rebuilds all system databases, including a fresh copy of the resource database, to a clean baseline state.

Alternative or Verification Steps

• After completing the steps above, run DBCC CHECKDB('mssqlsystemresource') WITH NO_INFOMSGS, ALL_ERRORMSGS; — it should now return no consistency errors.
• If corruption persists or recurs immediately, perform a full uninstall/reinstall of the SQL Server 2025 instance, then restore user databases from verified backups rather than continuing to repair the instance in place.

Troubleshooting Guide: DBCC Error 5245 — Lock Request Timeout

Why This Error Happens

Error 5245 occurs when DBCC CHECKDB (or related DBCC commands) cannot acquire the necessary schema or object lock on a table within the default lock timeout period, typically because another session is holding a conflicting lock due to a long-running transaction, blocking query, or heavy concurrent workload. This is common in Microsoft SQL Server 2025 environments running on Windows Server 2025 or Windows 11 when maintenance jobs overlap with active OLTP traffic. Since DBCC requires at least a schema stability (Sch-S) lock to read object metadata safely, any process holding an incompatible lock (such as Sch-M during DDL operations) will cause this timeout and force DBCC to skip the object.

Step-by-Step Resolution Guide

Step 1: Identify the Blocking Session

• Run the following query to find sessions holding locks on the affected object:

  SELECT request_session_id, resource_type, request_mode, request_status
  FROM sys.dm_tran_locks
  WHERE resource_database_id = DB_ID('YourDatabaseName');

• Cross-reference the request_session_id with sys.dm_exec_requests to identify the blocking query text and duration.

Step 2: Reschedule or Isolate the DBCC Job

• Move DBCC CHECKDB execution to a maintenance window with minimal concurrent activity (e.g., 2:00 AM–4:00 AM).
• In SQL Server Agent (via SSMS 21 or Azure Data Studio), edit the job step schedule under SQL Server Agent > Jobs > Properties > Schedules.
• Avoid running DBCC during active ETL loads, index rebuilds, or batch inserts.

Step 3: Increase Lock Timeout for the DBCC Session

• Before running DBCC manually, set a longer lock timeout threshold at the session level:

  SET LOCK_TIMEOUT 60000; -- 60 seconds, adjust as needed
  DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• This gives SQL Server more time to acquire the Sch-S lock without immediately skipping the object.

Step 4: Kill or Resolve the Blocking Process (If Safe)

• If the blocking session is an orphaned or stalled transaction, terminate it carefully:

  KILL <session_id>;

• Confirm with application owners before killing any session tied to production transactions to avoid data loss.

Step 5: Run DBCC with Reduced Impact Options

• Use the WITH TABLOCK option to reduce lock escalation overhead during the check:

  DBCC CHECKDB('YourDatabaseName') WITH TABLOCK, NO_INFOMSGS;

• Note this takes a database-wide shared lock, so still schedule during low-activity periods.

Alternative or Verification Steps

• Re-run DBCC CHECKDB immediately after resolving blocking sessions and confirm no Error 5245 messages appear in the output or in SQL Server Error Logs (Management > SQL Server Logs in SSMS).
• As an alternative, restore a recent database backup to a secondary instance and run DBCC CHECKDB there to validate integrity without contention, if production locking cannot be resolved quickly.

Troubleshooting Guide: Error 5244 – Repair Statement Not Processed (Read-Only Files)

Why This Error Happens

This error occurs when SQL Server’s DBCC CHECKDB repair process attempts to write corrective changes to database files that are flagged as read-only at either the file system or database level. This is common after restoring a database from a read-only backup source, working with files on read-only media, or when NTFS file permissions were altered. Since repair operations (REPAIRREBUILD or REPAIRALLOWDATALOSS) require write access to modify corrupted pages, SQL Server blocks the operation entirely to prevent an inconsistent repair state.

Step-by-Step Resolution Guide

Step 1: Verify the Database READ_ONLY Status

• Open SQL Server Management Studio (SSMS) or a query window connected to the instance.
• Run the following command to check the current state:

  SELECT name, is_read_only FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_read_only returns 1, proceed to Step 2 to change it.

Step 2: Set the Database to READ_WRITE Mode

• Execute the following T-SQL command, ensuring no other active connections are using the database:

  ALTER DATABASE YourDatabaseName SET READ_WRITE WITH ROLLBACK IMMEDIATE;

• The WITH ROLLBACK IMMEDIATE clause forcibly disconnects other users to apply the change instantly.

Step 3: Check NTFS File Permissions on the Physical Files

• Navigate to the default data directory, typically located at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\
• Right-click the .mdf and .ldf files, select Properties > Security.
• Ensure the NT SERVICE\MSSQLSERVER account (or your specific named instance service account) has Full Control permissions.
• Also verify the Read-only checkbox under the General tab is unchecked for these files.

Step 4: Confirm Disk and Volume Status

• Open Disk Management (diskmgmt.msc) on Windows Server 2025 or Windows 11.
• Verify the volume hosting the database files is not mounted in a read-only state (common with SAN snapshots or certain cloud-attached disks).
• If using Storage Spaces or a SAN LUN, confirm the volume attribute using DiskPart:

  diskpart
  list volume
  select volume X
  attributes volume

• If “Read-only” appears in the attributes, clear it using attributes volume clear readonly.

Step 5: Re-run the Repair Statement

• Once permissions and database mode are corrected, re-initiate the repair process:

  DBCC CHECKDB (YourDatabaseName, REPAIR_ALLOW_DATA_LOSS);

• Monitor the SQL Server Error Log to confirm the process completes without triggering Error 5244 again.

Alternative or Verification Steps

• Verification: After running the repair, execute DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; again to confirm consistency errors are resolved and the file write status remains stable.
• Alternative: If the files remain locked due to a third-party backup agent or antivirus software holding a handle on the .mdf/.ldf files, temporarily pause the agent/AV real-time scanning and retry the ALTER DATABASE command in Step 2.

Troubleshooting Guide: SQL Server Error 5243

Why This Error Happens

Error 5243 is a severe internal consistency error raised by the SQL Server Database Engine when it detects corruption or an unexpected state during an internal operation, often related to in-memory data structures, page allocation, or the buffer pool. This typically stems from underlying storage corruption, faulty RAM, a disk I/O subsystem failure, or a bug triggered by an unexpected code path during query execution. Because this is flagged as a “contact technical support” level error, it almost always indicates data integrity is at risk and requires immediate diagnostic action rather than a simple configuration fix.

Step-by-Step Resolution Guide

Step 1: Capture Diagnostic Information Immediately

• Open the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG and note the full text of the Error 5243 entry, including the associated stack dump file paths.
• Locate the corresponding .txt stack dump and SQLDumpXXXX.mdmp files in the LOG directory; these are required for root cause analysis.
• Check the Windows Event Viewer (Windows Logs > System and Application) for correlating hardware or disk warnings (Event ID 51, 129, or 153) occurring at the same timestamp.

Step 2: Run Hardware and Storage Integrity Checks

• Execute chkdsk C: /f /r (replace C: with your data drive) from an elevated Command Prompt and schedule a restart if the volume is in use.
• Run DCDIAG or vendor-specific diagnostic tools (e.g., Dell OpenManage, HPE iLO Diagnostics) to rule out failing RAM or RAID controller issues.
• Verify disk firmware and storage drivers are up to date, as outdated drivers are a common cause of silent I/O corruption on Windows Server 2025.

Step 3: Run DBCC CHECKDB to Identify Database-Level Corruption

• Open SQL Server Management Studio (SSMS) and run the following against the affected database:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Review the output for allocation or consistency errors reported alongside the 5243 event.
• If corruption is confirmed, restore the database from the most recent clean backup rather than attempting an in-place repair, since REPAIR_ALLOW_DATA_LOSS should only be used as a last resort.

Step 4: Apply the Latest SQL Server 2025 Cumulative Update

• Navigate to the Microsoft Update Catalog and download the latest Cumulative Update (CU) for SQL Server 2025.
• Install the update during a maintenance window, as it may contain a fix for the specific internal code path triggering Error 5243.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER after installation completes.

Alternative or Verification Steps

• To verify the fix, re-run DBCC CHECKDB on all user and system databases and confirm zero allocation or consistency errors are returned.
• If the error persists after hardware checks and patching, open a Microsoft Support (CSS) case and submit the SQLDumpXXXX.mdmp files from Step 1 for deeper binary-level analysis.

Troubleshooting Guide: SQL Server Error 5242 — Internal Inconsistency Detected

Why This Error Happens

Error 5242 indicates that SQL Server’s internal consistency-checking logic has detected a structural anomaly on a specific page during an operation, typically pointing to underlying data corruption. This corruption is most commonly caused by faulty storage hardware, disk controller errors, or an unexpected I/O interruption (such as a power failure or improper shutdown) that occurred while SQL Server was writing to disk. In rarer cases, it can also stem from a SQL Server engine defect or memory corruption affecting the buffer pool before the page was flushed to disk.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of Corruption

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify all affected objects and pages.
• Note the database ID and page ID (%S_PGID) referenced in the original error message for cross-reference.
• Check the SQL Server Error Log and Windows Event Viewer (Application log) for related I/O or disk-subsystem warnings around the same timestamp.

Step 2: Check the Underlying Storage and Hardware

• Open Server Manager > Tools > Event Viewer on Windows Server 2025/Windows 11 and inspect the System log for Disk, NTFS, or Storport errors.
• Run chkdsk D: /f /r (replace D: with the volume hosting your .mdf/.ldf files) during a maintenance window to scan for physical disk errors.
• If using virtualized or SAN storage, verify there were no recent storage failovers, firmware updates, or controller resets logged around the error time.
• Confirm Windows Server is fully patched via Settings > Windows Update, as storage driver fixes may resolve intermittent I/O corruption.

Step 3: Restore from a Known-Good Backup (Preferred Fix)

• Identify the most recent clean full backup: RESTORE HEADERONLY FROM DISK = 'E:\Backups\YourDB.bak';
• Restore to a test instance first: RESTORE DATABASE YourDatabaseName_Test FROM DISK = 'E:\Backups\YourDB.bak' WITH REPLACE, RECOVERY;
• Run DBCC CHECKDB against the restored test copy to confirm it’s clean before promoting it to production.

Step 4: Use DBCC CHECKDB Repair as a Last Resort

• If no valid backup exists, run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS, REPAIR_ALLOW_DATA_LOSS; only after taking a full backup of the current (corrupt) state.
• Understand this command may delete corrupted rows/pages to restore structural consistency — expect possible data loss.
• Re-run DBCC CHECKDB afterward to confirm the error no longer appears.

Alternative or Verification Steps

• After remediation, re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; and confirm it returns zero allocation/consistency errors.
• If Error 5242 recurs on the same page/LUN, treat it as a hardware fault — engage your storage vendor or migrate the database files to a different physical volume before restoring again.

Troubleshooting Guide: SQL Server Error 5241

Why This Error Happens

Error 5241 occurs when a DBCC SHRINKFILE operation specifies a target size that is larger than the current actual size of the database file, which is a logically invalid request since shrinking implies reducing size, not increasing it. This typically results from stale size calculations, incorrect scripting logic that pulls outdated file metadata, or manual entry errors when specifying the target size in KB. It can also occur in automated maintenance scripts where file size values were cached before a previous shrink or autogrowth event altered the actual file size.

Step-by-Step Resolution Guide

Step 1: Verify the Current Actual File Size

• Run the following query to get accurate, real-time file size data for the database in question:

  SELECT 
      database_id, 
      file_id, 
      name AS logical_name, 
      size * 8 AS size_kb 
  FROM sys.master_files 
  WHERE database_id = DB_ID('YourDatabaseName');

• Compare the size_kb output against the target shrink size you originally specified in your DBCC SHRINKFILE command.
• Confirm you are referencing the correct file_id and database_id reported in the error message, as mismatched IDs across multiple databases are a common cause.

Step 2: Correct the Target Size Parameter

• Adjust your shrink command so the target size is strictly less than or equal to the actual current file size retrieved in Step 1.
• Example corrected syntax:

  DBCC SHRINKFILE (N'YourLogicalFileName', <target_size_in_MB>);

• If the goal is simply to reclaim free space without specifying an exact size, omit the target size value entirely and let SQL Server calculate the minimum:

  DBCC SHRINKFILE (N'YourLogicalFileName');

Step 3: Refresh Cached Metadata in Automation Scripts

• If this error originated from a SQL Agent Job or a PowerShell/SSMS automation script, locate the step where the target size variable is calculated.
• Ensure the script queries sys.master_files or sys.database_files immediately before execution rather than using a hardcoded or previously stored value.
• Update any configuration files or job step parameters located in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log related logs to confirm no stale values are being passed via job tokens.

Step 4: Check for Concurrent Autogrowth Events

• Review the SQL Server Error Log (via SSMS Object Explorer > Management > SQL Server Logs) for AUTOGROW events that may have increased the file size after your script calculated the shrink target.
• If autogrowth is frequently interfering with shrink operations, consider temporarily disabling autogrowth or coordinating shrink maintenance windows to avoid active write/growth periods on the file.

Alternative or Verification Steps

• After re-running the corrected DBCC SHRINKFILE command, re-query sys.master_files to confirm the size_kb value matches or is close to your intended target, confirming the shrink succeeded without error.
• As an alternative, use the SSMS GUI (Right-click Database > Tasks > Shrink > Files) which automatically populates the Current allocated space field, preventing manual entry of an invalid target size.

Why This Error Happens

Error 5240 occurs when SQL Server 2025 detects a concurrency conflict on a specific database file—typically because two shrink operations (DBCC SHRINKFILE, DBCC SHRINKDATABASE, or an Autoshrink job) are attempting to target the same File ID simultaneously. It can also trigger if the file is already at its minimum possible size (effectively “empty” of reclaimable space), or if a previous shrink process crashed and left an internal lock flag active on the file metadata. This is a logical locking issue within SQL Server’s internal engine rather than a Windows OS-level permission failure.

Step-by-Step Resolution Guide

Step 1: Identify Active Shrink Processes

• Open SQL Server Management Studio (SSMS) and run a query against sys.dm_exec_requests to check for existing commands with the command column showing DbccFilesCompact or DbccSpaceReclaim.
• Execute: SELECT session_id, command, percent_complete FROM sys.dm_exec_requests WHERE command LIKE '%SHRINK%';
• If a session is found, do not kill it immediately; allow it to finish, as killing a shrink mid-process can cause database inconsistency.

Step 2: Verify Autoshrink Configuration Conflicts

• Check if the AUTO_SHRINK database property is enabled, as this background process often fights with manual DBA-initiated shrinks.
• Navigate to SSMS > Database Properties > Options > Automatic > Auto Shrink, and set this to False.
• Alternatively, run: ALTER DATABASE [YourDB] SET AUTO_SHRINK OFF;

Step 3: Disable and Isolate the Shrink Operation

• Stop any SQL Server Agent Jobs scheduled to run maintenance plans that include “Shrink Database” tasks, as these often queue up during peak hours causing file locks.
• Open SQL Server Agent > Jobs in SSMS and disable any job containing the Shrink task type temporarily.
• Attempt the shrink again using a single-threaded approach: DBCC SHRINKFILE (N'YourFileLogicalName', 0);

Step 4: Restart the SQL Server Service to Clear Locks

• If the File ID remains locked with no active process found in Step 1 (orphaned lock), restart the service to clear the internal engine state.
• Open Services.msc on Windows Server 2025, locate “SQL Server (MSSQLSERVER),” right-click, and select Restart.
• Note: This requires a maintenance window as it will drop all active connections.

Alternative or Verification Steps

• Verify the Fix: Run DBCC SHRINKFILE again immediately after the restart; if the file size (reported in sys.database_files) decreases without throwing Error 5240, the lock has been cleared successfully.
• Alternative Solution: If the file is genuinely “empty” and cannot be shrunk further, this is expected behavior (not a bug); verify the size vs. used_pages in sys.database_files to confirm the file is already at its minimum logical size.

Troubleshooting Guide: Error 5239 in Microsoft SQL Server 2025

Why This Error Happens

Error 5239 occurs when a DBCC command (such as DBCC CHECKTABLE, DBCC SHOWCONTIG, or DBCC CLEANTABLE) is executed against an object type it was never designed to support, such as a view, a system table, a queue object, or an internal table associated with Service Broker or In-Memory OLTP. This is not a corruption issue but rather a mismatch between the command’s supported scope and the target object’s metadata type. It frequently appears after automated maintenance scripts loop through sys.objects without filtering out unsupported object types like views (type = ‘V’) or synonyms.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Object Causing the Error

• Run the following query to pull the object name and type from the error message’s Object ID:

SELECT name, type, type_desc 
FROM sys.objects 
WHERE object_id = <ObjectID_from_error>;

• Confirm whether type_desc returns something other than USER_TABLE (e.g., VIEW, SYSTEM_TABLE, INTERNAL_TABLE, or SERVICE_QUEUE).

Step 2: Audit and Correct the Maintenance Script

• Open the SQL Agent Job or maintenance plan (often located via SQL Server Management Studio under SQL Server Agent > Jobs) that triggered the DBCC command.
• Modify the cursor or loop logic to explicitly filter for supported types only, for example:

WHERE type = 'U' -- Excludes Views, System Tables, and Internal Tables

• Save the job step and re-validate the T-SQL syntax using the Parse button in SSMS.

Step 3: Exclude Views and Internal Objects Manually

• If you are running a manual DBCC command (like DBCC CHECKTABLE), ensure you are not accidentally running it against a View or a Hekaton (In-Memory) table.
• Use sp_helptext or query sys.views separately to ensure the object ID does not belong to a view, as DBCC CHECKTABLE does not operate on views.

Step 4: Update SQL Server 2025 Cumulative Updates

• Navigate to the Microsoft Update Catalog or use the SQL Server Installation Center to check for the latest Cumulative Update (CU) for SQL Server 2025.
• Install any pending patches, as early 2025 builds occasionally misclassify internal memory-optimized table types, triggering false positives for Error 5239.

Alternative or Verification Steps

• Verification: Re-run the specific DBCC command against a known standard user table (type = 'U') in the same database to confirm the command executes successfully, isolating the issue to the specific object type.
• Alternative: If the target is a View, use DBCC CHECKCONSTRAINTS or sp_refreshview instead of DBCC CHECKTABLE, as integrity checks on views require different validation commands than standard tables.

Why This Error Happens

This error occurs when a maintenance routine—typically an index rebuild/reorganize script, statistics update job, or DBCC command—attempts to operate on an object ID that SQL Server resolves to a stored procedure or user-defined function rather than a table or indexed view. It is almost always caused by a custom T-SQL maintenance script (often from Ola Hallengren’s solution or a homegrown job) that queries sys.objects or sys.indexes without properly filtering on type IN ('U', 'V'), causing it to pick up P (procedure) or FN/TF (function) object types. In rarer cases, metadata corruption in sys.objects after an in-place upgrade to SQL Server 2025 or a crashed transaction can leave stale object ID references that point to the wrong object class.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Object and Job

• Run SELECT name, object_id, type, type_desc FROM sys.objects WHERE object_id = <ID from error message> to confirm the object is indeed a procedure or function.
• Check SQL Server Agent job history (msdb.dbo.sysjobhistory) or Windows Event Viewer under Applications and Services Logs > Microsoft > SQL Server to identify which job or script triggered error 5238.
• Note the exact maintenance script (index rebuild, update statistics, or DBCC CHECKDB/CHECKTABLE) that was executing at the time.

Step 2: Correct the Maintenance Script Filtering Logic

• Open the index/statistics maintenance script (commonly IndexOptimize.sql or a custom T-SQL job step) in SSMS 21 or Azure Data Studio.
• Locate the cursor or loop that selects objects for maintenance, and ensure it explicitly filters object types:
  WHERE o.type IN ('U', 'V')
  and excludes 'P', 'FN', 'TF', and 'IF'.
• If using Ola Hallengren’s scripts, update to the latest release (compatible with SQL Server 2025) since older builds may not account for new system object types introduced in this version.
• Save the corrected script and redeploy it to the SQL Server Agent job step.

Step 3: Validate System Catalog Integrity

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS to rule out metadata corruption in sys.objects or related catalog views.
• If corruption is found, restore from a known-good backup taken before the upgrade to SQL Server 2025, or use DBCC CHECKDB repair options only as a last resort after taking a full backup.
• Rebuild the plan cache with DBCC FREEPROCCACHE if stale compiled plans referencing incorrect object types are suspected.

Step 4: Re-run the Maintenance Job

• Manually execute the corrected index/statistics maintenance job via SQL Server Agent (right-click the job > Start Job at Step…).
• Monitor the job output or SSMS Messages tab for completion without error 5238.

Alternative or Verification Steps

• Query sys.dm_exec_requests and the SQL Server Error Log while the job reruns to confirm no further object_id resolution errors occur.
• If the error persists after script correction, as a workaround, add explicit object name validation (OBJECTPROPERTY(object_id, 'IsProcedure') = 0) inside the script before calling ALTER INDEX or UPDATE STATISTICS, then escalate to Microsoft Support with the CSS diagnostic package if the root cause traces back to system catalog corruption.

Troubleshooting Guide: DBCC Error 5237 in Microsoft SQL Server 2025

Why This Error Happens

Error 5237 occurs when DBCC CHECKDB performs cross-rowset validation (comparing data pages against their corresponding index pages) and encounters an internal query processor failure rather than an actual data consistency mismatch. This is typically triggered by tempdb resource starvation, memory pressure during the parallel check operation, or an underlying corruption in system catalog views that DBCC relies on to build its internal query plan. In rare cases, outdated SQL Server 2025 cumulative updates contain known query optimizer bugs that surface specifically during cross-rowset validation on large or heavily fragmented tables.

Step-by-Step Resolution Guide

Step 1: Verify Tempdb Health and Capacity

• Open SQL Server Management Studio (SSMS) and check tempdb free space via sys.dm_db_file_space_usage.
• Ensure tempdb data files have sufficient autogrowth headroom (recommend at least 20% free).
• If tempdb is constrained, add additional data files under Server Properties > Database Settings or via:

  ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'D:\SQLData\tempdb2.ndf', SIZE = 2048MB)

Step 2: Re-run DBCC CHECKDB in Single-Threaded Mode

• The error can stem from parallel query execution during the internal cross-rowset scan.
• Force single-threaded execution to isolate whether parallelism is the trigger:

  DBCC CHECKDB ('YourDatabaseName') WITH MAXDOP = 1, NO_INFOMSGS, ALL_ERRORMSGS

• Review the output for whether Error 5237 recurs or resolves.

Step 3: Apply Latest Cumulative Update for SQL Server 2025

• Check your current build via:

  SELECT SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('ProductUpdateLevel')

• Download and install the latest Cumulative Update (CU) from the Microsoft Update Catalog, as several CUs address internal query errors in DBCC’s cross-rowset validation logic.
• Reboot Windows Server 2025 (or Windows 11) after installation to ensure SQL Server services fully reload updated binaries.

Step 4: Rebuild Affected Indexes and Statistics

• If the error references a specific object ID, identify the table/index using:

  SELECT OBJECT_NAME(object_id) FROM sys.objects WHERE object_id = <ObjectID>

• Rebuild the index to eliminate potential internal fragmentation causing the query engine to misread rowset mappings:

  ALTER INDEX ALL ON YourTableName REBUILD WITH (ONLINE = ON)

• Update statistics afterward with UPDATE STATISTICS YourTableName WITH FULLSCAN.

Step 5: Validate System Catalog Integrity

• Run DBCC CHECKCATALOG ('YourDatabaseName') to confirm there is no metadata corruption feeding bad data into the cross-rowset check.
• If catalog corruption is found, restore the database from the last known good backup rather than attempting manual repair.

Alternative or Verification Steps

• Re-run DBCC CHECKDB with WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY after completing the steps above; a clean pass with zero allocation/consistency errors confirms resolution.
• If Error 5237 persists after a CU update and index rebuild, open a Microsoft Support (CSS) ticket referencing the build number and attach the full DBCC output, as this may indicate an unreported optimizer regression specific to SQL Server 2025.

Troubleshooting Guide: SQL Server Error 5236 – “Unable to Process Object Because It Is a Four-Part Name”

Why This Error Happens

This error occurs when a Data Definition Language (DDL) statement or system function attempts to reference a table or object using a four-part naming convention (Server.Database.Schema.Object) against a linked server, but the target provider does not support remote DDL operations. It is commonly triggered when the operation requires a schema change (like ALTER TABLE or TRUNCATE TABLE) rather than a simple data query, as OLE DB providers typically only support pass-through queries (SELECT/INSERT/UPDATE) for remote objects, not structural modifications. Additionally, this can happen if the linked server configuration lacks the necessary provider options to allow “nested queries” or DDL pass-through.

Step-by-Step Resolution Guide

Step 1: Verify the Linked Server Provider Capabilities

• Confirm that the OLE DB provider used for the linked server supports the specific DDL command you are trying to run.
• Open SQL Server Management Studio (SSMS) and navigate to Server Objects > Linked Servers > Providers.
• Right-click the specific provider (e.g., MSOLEDBSQL or SQLNCLI) and check Properties to ensure “Supports DDL” and “Nested Queries” are enabled if available for that provider.

Step 2: Rewrite the Query Using OPENQUERY

• Since four-part names often fail for DDL commands, bypass the limitation by wrapping the command in a pass-through query.
• Instead of running ALTER TABLE LinkedServer.DB.Schema.Table, use the OPENQUERY syntax to send the command directly to the remote server’s native SQL engine.
• Example syntax:

EXEC ('ALTER TABLE Schema.Table ADD Column INT') AT [LinkedServerName];

Step 3: Check Server Compatibility Level and Permissions

• Ensure the local SQL Server 2025 instance and the remote instance (if applicable) are not restricted by a legacy compatibility level that blocks modern four-part name resolution.
• Verify the login mapped to the linked server has db_ddladmin or sysadmin rights on the remote database, as insufficient permissions can sometimes manifest as a resolution error rather than a standard permission denial.
• Run the following T-SQL to check current linked server configurations:

EXEC sp_linkedservers;
EXEC sp_helpserver;

Step 4: Validate Network and DCOM Configuration (Windows Server 2025)

• If the linked server is an external data source, ensure that DCOM settings on Windows Server 2025 are not blocking the “Ad Hoc Distributed Queries” feature.
• Open dcomcnfg.exe, navigate to Component Services > Computers > My Computer > DCOM Config, and verify the MSDAINITIALIZE permissions allow the SQL Server Service Account local activation rights.

Alternative or Verification Steps

• Verification: Execute the query again using the 3-part name locally on the target server (bypassing the linked server architecture) to confirm the object and syntax are valid outside the linking context.
• Alternative: If DDL pass-through continues to fail, use PolyBase or Linked Server Synonym objects in SQL Server 2025 to abstract the four-part name into a two-part name, which often resolves provider-specific parsing errors.

Troubleshooting Guide: SQL Server Error 5235 — DBCC Command Terminated Abnormally

Why This Error Happens

Error 5235 occurs when a DBCC command (such as DBCC CHECKDB, CHECKTABLE, or CHECKALLOC) is interrupted before completion, typically due to an internal error state triggered by database corruption, insufficient tempdb space, or a killed session/connection timeout. This error is frequently seen on SQL Server 2025 instances running on Windows Server 2025 when the underlying storage subsystem experiences I/O latency spikes or when the DBCC process is manually terminated via KILL command or client disconnection. In some cases, this is also a symptom of insufficient memory grants causing the internal consistency check worker threads to fail mid-execution.

Step-by-Step Resolution Guide

Step 1: Verify the SQL Server Error Log and Event Viewer

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Cross-reference the exact error state number (the %d value) with Microsoft’s documented DBCC error states list to identify if it’s a corruption-related state (e.g., state 1, 4, 8) or an environmental state (e.g., state 11, 12).
• Check Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for correlating I/O or memory warnings around the same timestamp.

Step 2: Check TempDB Space and Configuration

• Run the following query to confirm tempdb isn’t running out of space, which is a leading cause of DBCC termination:

SELECT name, size/128.0 AS size_MB, max_size, growth 
FROM tempdb.sys.database_files;

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ (adjust version path for SQL Server 2025) and ensure the drive hosting tempdb has at least 20-30% free space.
• If space is low, add an additional tempdb data file or expand the existing file via SSMS > Database Properties > Files.

Step 3: Rule Out Manual Termination or Timeout Conflicts

• Confirm no automated job, monitoring tool, or administrator issued a KILL command against the DBCC session ID during execution.
• Check for any configured query governor cost limit or remote query timeout settings in Server Properties > Connections that may be prematurely closing long-running DBCC sessions.
• Increase the client connection timeout value in your application or SSMS connection properties if DBCC is being run interactively and timing out.

Step 4: Run DBCC CHECKDB with Repair Options (If Corruption Confirmed)

• If the error state correlates to actual corruption, take the database offline or set it to single-user mode:

ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ('YourDB', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE [YourDB] SET MULTI_USER;

• Always restore from a verified backup first if data loss is unacceptable, using repair only as a last resort.

Alternative or Verification Steps

• Re-run DBCC CHECKDB during a low-activity window and monitor it to completion using sys.dm_exec_requests to confirm the percent_complete column progresses without interruption.
• If the issue persists, restore the database to a separate instance from the last known good backup and run DBCC CHECKDB there to isolate whether the root cause is environmental (Windows Server 2025 host) or data-level corruption.

Troubleshooting Guide: Error 5234 — DBCC SHRINKDATABASE File Skipped During Space Allocation Adjustment

Why This Error Happens

Error 5234 typically occurs when SQL Server 2025 cannot acquire the necessary locks on a database file because another process—such as an active transaction, an index rebuild, or a concurrent backup—is holding a conflicting lock. This can also happen when the data file is close to its maximum configured size, has autogrowth disabled, or resides on storage with insufficient free space, preventing SQL Server from adjusting the allocation map. In some cases, file system-level permission restrictions on the Windows Server 2025 host (especially with newly enforced security defaults) block the SQL Server service account from modifying file metadata.

Step-by-Step Resolution Guide

Step 1: Check for Blocking Transactions and Active Sessions

• Run sp_who2 or query sys.dm_exec_requests to identify sessions holding locks on the target database.
• Look specifically for long-running transactions, open index maintenance jobs, or replication processes.
• Terminate or wait out blocking sessions using KILL <session_id> only after confirming it is safe to do so.

Step 2: Verify File Growth Settings and Disk Space

• Open SSMS, navigate to Database Properties > Files, and confirm “Autogrowth/Maxsize” is not restricted to a value smaller than required.
• Check the underlying disk volume on Windows Server 2025 using Get-PSDrive in PowerShell to confirm sufficient free space exists.
• If the file has hit MAXSIZE, increase it manually:
• ALTER DATABASE [YourDB] MODIFY FILE (NAME = 'YourFileLogicalName', MAXSIZE = UNLIMITED);

Step 3: Validate File System Permissions for the SQL Server Service Account

• Navigate to the data file directory (commonly C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA).
• Right-click the .mdf/.ldf files, go to Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Reapply permissions if needed using icacls:
• icacls "D:\SQLData\YourDB.mdf" /grant "NT SERVICE\MSSQLSERVER:(F)"

Step 4: Retry Shrink with Lower Load and Explicit File Targeting

• Run the shrink operation during low-activity hours, targeting the specific file ID reported in the error:
• DBCC SHRINKFILE (N'YourFileLogicalName', TRUNCATEONLY);
• Avoid using DBCC SHRINKDATABASE broadly; shrink problematic files individually to reduce lock contention.

Step 5: Check for Corruption on the Target File

• Run DBCC CHECKDB('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying corruption preventing allocation changes.
• If corruption is found, restore from a verified backup rather than continuing shrink operations.

Alternative or Verification Steps

• After applying fixes, re-run DBCC SHRINKDATABASE (YourDB) and confirm no Error 5234 appears in the output or SQL Server Error Log (EXEC xp_readerrorlog 0, 1, N'5234';).
• If the issue persists, consider rebuilding indexes first with ALTER INDEX ALL ON YourTable REBUILD; to release fragmented space naturally, then retry the shrink as an alternative path.

Troubleshooting Guide: SQL Server Error 5233 – Table Error: Alloc Unit ID Corruption

Why This Error Happens

Error 5233 occurs when SQL Server’s consistency checker (DBCC CHECKDB) detects structural corruption within an allocation unit, meaning the internal page-chain linkage for a table or index no longer matches what the storage engine expects. This is typically caused by underlying disk I/O failures, improper shutdowns, faulty storage drivers, or hardware-level bit flips that corrupt page headers before SQL Server can flush them to disk. In rare cases, it can also stem from a bug in a hardware RAID controller or a failing SSD/NVMe cache layer on Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of Corruption

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFRONTEND, ALL_ERRORMSGS, DATA_PURITY; in SQL Server Management Studio (SSMS) or sqlcmd.
• Note the specific alloc unit ID, object ID, and index ID reported in the error output.
• Cross-reference the object ID using SELECT OBJECT_NAME(object_id) FROM sys.allocation_units WHERE allocation_unit_id = <ID>; to identify the affected table.

Step 2: Check Hardware and Storage Health

• Open Event Viewer on Windows Server 2025 (eventvwr.msc) and inspect the System log for Disk, NTFS, or Storport warnings around the time of corruption.
• Run chkdsk D: /f /r (replace D: with the volume hosting your .mdf/.ldf files) during a maintenance window to scan for physical sector errors.
• Verify storage controller/SSD firmware is up to date via Windows Update or vendor tools (e.g., Dell OpenManage, HPE iLO).

Step 3: Restore from a Clean Backup (Preferred Fix)

• Take a tail-log backup first if the database is still online: BACKUP LOG YourDatabaseName TO DISK = 'D:\Backups\TailLog.trn' WITH NORECOVERY;
• Restore the last known good full backup plus subsequent log backups up to the point before corruption occurred.
• Validate restored database immediately with DBCC CHECKDB ('YourDatabaseName') WITH ALL_ERRORMSGS;

Step 4: Use Repair as a Last Resort

• If no clean backup exists, run DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) only after putting the database in single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Understand this may delete corrupted rows/pages to restore structural integrity, resulting in data loss.
• Set the database back to multi-user mode afterward: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Alternative or Verification Steps

• Re-run DBCC CHECKDB ('YourDatabaseName') WITH ALL_ERRORMSGS; after remediation — a clean pass with zero allocation errors confirms the fix.
• If corruption recurs on the same LUN/volume, migrate the database files to a different physical disk or storage array to rule out persistent hardware failure.

Why This Error Happens

Error 5232 occurs because DBCC CHECKDB requires an internal database snapshot to perform catalog and Service Broker consistency checks, and this snapshot creation fails when there is insufficient disk space on the volume hosting the database files or when the SQL Server service account lacks the NTFS permissions needed to create sparse files. This error also triggers intentionally when the WITH TABLOCK option is specified, since TABLOCK forces CHECKDB to skip snapshot-based checks in favor of table locks, which are incompatible with full catalog validation. Additionally, corrupted or missing VSS components on Windows Server 2025 / Windows 11 can prevent the snapshot subsystem from initializing correctly.

Step-by-Step Resolution Guide

Step 1: Verify Disk Space and Snapshot Location

• Check the volume where the database data files (.mdf/.ndf) reside — the snapshot is created on the same volume by default.
• Run SELECT * FROM sys.dm_os_volume_stats(DB_ID(), 1); to confirm free space is at least equal to the size of the largest table/index in the database.
• Free up space or move non-essential files off the volume if space is below the required threshold.

Step 2: Remove WITH TABLOCK from the CHECKDB Command

• Open SQL Server Management Studio (SSMS) and locate the job or script running DBCC CHECKDB.
• Replace any command like:

  DBCC CHECKDB ('YourDatabase') WITH TABLOCK;

with:

  DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Re-run the job to confirm CHECKDB now performs full catalog and Service Broker checks.

Step 3: Validate SQL Server Service Account Permissions

• Open Computer Management > Local Users and Groups (or Active Directory Users and Computers for domain accounts) on Windows Server 2025 / Windows 11.
• Confirm the SQL Server service account has Full Control NTFS permissions on the folder path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\
• Restart the SQL Server service via services.msc after applying permission changes.

Step 4: Check and Repair the Volume Shadow Copy Service (VSS)

• Open an elevated Command Prompt and run:

  vssadmin list writers

• Confirm the “SqlServerWriter” status shows [Stable] with no errors; if not, restart the SQL Server VSS Writer service in services.msc.
• If errors persist, re-register VSS DLLs using:

  regsvr32 /i vssvc.exe

Alternative or Verification Steps

• Run DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS; again and confirm no Error 5232 appears in the Results pane or SQL Server Error Log.
• If snapshot creation still fails due to persistent space constraints, use WITH PHYSICAL_ONLY as a temporary alternative, understanding it skips full catalog/Service Broker validation until the underlying space/permission issue is resolved.

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.

Troubleshooting Guide: Error 5230 — DBCC CHECKCATALOG Cannot Be Run on tempdb

Why This Error Happens

This error occurs because DBCC CHECKCATALOG is designed to validate consistency between system catalog tables in user and system databases, but tempdb does not maintain persistent catalog metadata in the same way since it is recreated at every SQL Server service restart. SQL Server 2025 explicitly blocks this operation on tempdb because the catalog consistency checks are not applicable to a database that is rebuilt from scratch on startup. This is typically a scripting or automation error where a maintenance job or custom script is looping through all databases (including tempdb) and calling DBCC CHECKCATALOG indiscriminately, rather than excluding system databases that don’t support the check.

Step-by-Step Resolution Guide

Step 1: Identify the Source of the DBCC Call

• Check SQL Server Agent Job steps, Maintenance Plans, or Ola Hallengren’s scripts for a loop that iterates over sys.databases.
• Look specifically for T-SQL blocks using a cursor or sp_MSforeachdb that calls DBCC CHECKCATALOG('tempdb').
• Review the SQL Server Error Log (Management > SQL Server Logs in SSMS) to confirm the exact timestamp and originating session.

Step 2: Modify the Script to Exclude tempdb

• Add a WHERE clause filter to exclude database_id = 2 (tempdb) from any dynamic SQL generation.
• Example filter for custom scripts:

  SELECT name FROM sys.databases WHERE name <> 'tempdb' AND state = 0

• If using Ola Hallengren’s DatabaseIntegrityCheck stored procedure, ensure the @CheckCommands parameter does not explicitly force CHECKCATALOG on system databases without using the built-in @Databases = 'SYSTEM_DATABASES' logic, which already handles this exclusion correctly.

Step 3: Run DBCC CHECKDB Instead for tempdb Validation

• Since CHECKCATALOG is unsupported, use DBCC CHECKDB(tempdb) instead if you need to validate the physical and logical integrity of temporary objects.
• Execute the following in a New Query window in SSMS:

  DBCC CHECKDB ('tempdb') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Note that CHECKDB inherently skips the catalog-specific checks for tempdb but still validates allocation and structural integrity.

Step 4: Update SQL Server Agent Job Schedules

• Navigate to SQL Server Agent > Jobs in SSMS.
• Open the properties of the failing job and update the Command text in the Job Step to reflect the corrected script from Step 2.
• Save the job and manually trigger a test run via right-click > Start Job at Step… to confirm the error no longer appears in the job history.

Alternative or Verification Steps

• Verify the fix by manually executing the corrected maintenance script or job; the job history in SSMS should show a “Succeeded” status with no Error 5230 referenced in the output log.
• As an alternative, if using third-party backup/maintenance tools (e.g., Redgate, Idera), check for a software update or patch, as vendor-specific tools may need a version update to recognize the stricter tempdb restrictions introduced in SQL Server 2025.

Troubleshooting Guide: SQL Server Error 5228 — “Table Error: Object ID %d”

Why This Error Happens

Error 5228 occurs when SQL Server’s consistency checker (DBCC CHECKDB/CHECKTABLE) detects structural corruption in a table’s data or index pages, typically caused by underlying disk I/O failures, faulty storage hardware, or improper shutdowns. This can also result from corrupted page headers, mismatched row offsets, or bit-level corruption introduced by antivirus software scanning database files. In rare cases, it stems from bugs in storage drivers or filesystem-level corruption on Windows Server 2025 when using ReFS or NTFS volumes with misconfigured caching policies.

Step-by-Step Resolution Guide

Step 1: Identify the Scope of Corruption

• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; in SQL Server Management Studio (SSMS) to confirm the full extent of damage.
• Note the specific Object ID and Index ID referenced in the error message for targeted repair.
• Check the SQL Server error log (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating I/O or disk warnings.

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

• Verify your most recent full backup predates the corruption event using RESTORE HEADERONLY FROM DISK = 'C:\Backups\YourDB.bak';.
• Perform a restore sequence: full backup, followed by differential and transaction log backups, using WITH NORECOVERY until the final log restore.
• Validate backup integrity using RESTORE VERIFYONLY FROM DISK = 'C:\Backups\YourDB.bak'; before committing to this as your recovery path.

Step 3: Run Targeted Repair if No Clean Backup Exists

• Set the database to single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;.
• Execute DBCC CHECKTABLE ('YourTableName', REPAIR_ALLOW_DATA_LOSS); only as a last resort, since this may permanently delete corrupted rows.
• Revert the database back to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;.

Step 4: Investigate Hardware and Storage Health

• Open Windows Server 2025 Event Viewer and check the System log under Windows Logs > System for Disk, NTFS, or Storport errors around the time of corruption.
• Run chkdsk C: /f /r (adjust drive letter accordingly) during a scheduled maintenance window to repair filesystem-level issues.
• Confirm your SAN/RAID controller firmware and drivers are updated to the latest vendor-supported version.

Step 5: Exclude SQL Server Files From Antivirus Scanning

• Navigate to your antivirus console and add exclusions for .mdf, .ldf, and .ndf file extensions.
• Exclude the SQL Server data directory, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Restart the SQL Server service via services.msc to ensure the exclusion takes effect cleanly.

Alternative or Verification Steps

• Re-run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; after remediation — a clean result with zero allocation or consistency errors confirms the fix.
• If corruption recurs frequently, consider migrating the database files to a different physical storage volume and enable Page Verify CHECKSUM (ALTER DATABASE YourDatabaseName SET PAGE_VERIFY CHECKSUM;) to catch future I/O corruption early.

Troubleshooting Guide: SQL Server Error 5229

Why This Error Happens

Error 5229 occurs when SQL Server’s consistency checker (DBCC) detects structural corruption within a table or index object, typically caused by damaged data pages, allocation unit inconsistencies, or interrupted I/O operations. This is often the result of underlying storage failures, improper shutdowns, faulty disk hardware, or filesystem-level corruption affecting the database file. In some cases, it may also stem from memory corruption during a write operation that was flushed to disk before detection.

Step-by-Step Resolution Guide

Step 1: Run a Full Database Consistency Check

• Open SQL Server Management Studio (SSMS) or a query window connected to the affected instance.
• Execute DBCC CHECKDB ('YourDatabaseName') WITH ALL_ERRORMSGS, NO_INFOMSGS;
• Review the output to identify the full scope of corruption (single object or multiple).
• Note the Object ID referenced in Error 5229 for further isolation.

Step 2: Isolate and Attempt Targeted Repair

• Identify the corrupted object using SELECT OBJECT_NAME(object_id) FROM sys.objects WHERE object_id = <YourObjectID>;
• Take the database into single-user mode via ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Run DBCC CHECKDB ('YourDatabaseName', REPAIR_REBUILD); first, as this avoids data loss.
• If REPAIR_REBUILD fails, escalate to DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS); only as a last resort.

Step 3: Restore From a Clean Backup (If Repair Fails)

• Navigate to your backup storage location (e.g., D:\SQLBackups\YourDatabaseName.bak).
• Restore the most recent verified backup using RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\SQLBackups\YourDatabaseName.bak' WITH REPLACE;
• Apply any available transaction log backups to minimize data loss.
• Set the database back to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 4: Check Underlying Hardware and Storage Health

• Open Windows Server 2025 Event Viewer and inspect the System log for Disk or NTFS warnings (Event IDs 7, 11, 51).
• Run chkdsk D: /f /r (replace with your actual SQL data drive) during a maintenance window to repair filesystem errors.
• Verify SAN/RAID controller health through vendor diagnostic tools if using enterprise storage.

Alternative or Verification Steps

• Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS; after remediation to confirm zero allocation or consistency errors are returned.
• If corruption recurs frequently, enable CHECKSUM page verification (ALTER DATABASE YourDatabaseName SET PAGE_VERIFY CHECKSUM;) and schedule nightly DBCC CHECKDB jobs via SQL Server Agent to catch issues early.

Troubleshooting Guide: SQL Server Error 5226 — UNLINKEDREORGPAGE Deallocation Failure

Why This Error Happens

Error 5226 occurs when SQL Server’s DBCC CHECKDB repair process (typically REPAIR_ALLOW_DATA_LOSS) encounters a page marked as UNLINKED_REORG_PAGE that cannot be removed from the allocation structures because its linkage in the IAM (Index Allocation Map) or PFS (Page Free Space) pages is inconsistent or corrupted. This usually stems from underlying physical corruption caused by storage subsystem failures, improper shutdowns, or disk I/O errors that occurred during a previous index reorganization or page split operation. In most cases, this is a symptom of deeper database corruption rather than an isolated, standalone issue.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of Corruption

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS to get a full list of errors, not just Error 5226.
• Check the SQL Server error log (via SSMS or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) for correlating I/O or storage warnings around the time corruption began.
• Note the specific Page ID and File ID referenced in the error (%d:%d) for targeted investigation.

Step 2: Rule Out Hardware and Storage Issues

• Check Windows Event Viewer (Event IDs 7, 9, 11, 15, 51, 129) under System logs for disk controller or storage driver errors.
• If using virtualized storage (Hyper-V/Azure), verify the underlying VHDX or storage pool integrity using Repair-VirtualDisk or your SAN vendor’s diagnostic tools.
• Ensure Windows Server 2025 storage drivers and firmware are fully updated before proceeding with repairs.

Step 3: Restore From a Known Good Backup (Preferred Method)

• If a recent, uncorrupted full backup exists, restore the database instead of repairing in place:
• RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabaseName.bak' WITH REPLACE, RECOVERY
• Apply any available transaction log backups to minimize data loss.
• This avoids forced data loss inherent to REPAIR_ALLOW_DATA_LOSS.

Step 4: Run Repair Only if No Backup Is Available

• Set the database to single-user mode:
• ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
• Execute the repair command:
• DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS)
• Return the database to multi-user mode:
• ALTER DATABASE YourDatabaseName SET MULTI_USER
• Note: this operation can delete the affected unlinked page and any associated rows, so expect potential data loss on the impacted table.

Step 5: Rebuild Affected Indexes

• After repair, rebuild all indexes on the table associated with the corrupted page to ensure structural integrity:
• ALTER INDEX ALL ON YourTableName REBUILD
• This resolves any residual fragmentation or orphaned allocation entries left behind by the repair process.

Alternative or Verification Steps

• Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS after remediation; a clean result with zero allocation or consistency errors confirms the fix.
• If corruption recurs shortly after repair, treat it as a hardware-level issue — run chkdsk /f /r on the underlying volume and consult your storage vendor, as this points to a failing disk or controller rather than a SQL Server defect.

Troubleshooting Guide: SQL Server Error 5227 – BULKOPERATIONPAGE Deallocation Failure

Why This Error Happens

Error 5227 occurs when SQL Server’s storage engine attempts to deallocate a BULKOPERATIONPAGE during a minimally logged bulk insert or bulk copy operation, but the page metadata in the system catalog (sys.allocation_units or PFS/GAM pages) has become inconsistent. This typically results from database corruption caused by abrupt I/O interruptions, storage subsystem failures, or antivirus/backup software locking data files mid-transaction. It can also surface after an unclean shutdown of the SQL Server service on Windows Server 2025 while a bulk-logged recovery model transaction was in flight.

Step-by-Step Resolution Guide

Step 1: Verify Database Integrity Immediately

• Open SQL Server Management Studio (SSMS) or use sqlcmd and run:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Review output for additional allocation errors (Error 8909, 8928, or 2575) that often accompany 5227.
• Check the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for correlating I/O warnings.

Step 2: Isolate the Affected File Group and Disk Subsystem

• Confirm the physical disk health using Windows Server 2025’s Get-PhysicalDisk PowerShell cmdlet to rule out hardware-level corruption.
• Check Event Viewer under Windows Logs > System for Disk or NTFS warning events (Event ID 7 or 153) occurring at the same timestamp as Error 5227.
• If using virtualized storage (Hyper-V/Azure), validate that the VHDX or disk is not thin-provisioned and running out of space.

Step 3: Repair the Corrupted Page Allocation

• Take the database offline for a consistent repair window:

  ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DBCC CHECKDB ('YourDatabaseName', REPAIR_REBUILD);

• If REPAIR_REBUILD does not resolve the page error, escalate to:

  DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);

• Only use REPAIR_ALLOW_DATA_LOSS as a last resort since it may delete the corrupted page and its data.

Step 4: Restore From a Clean Backup (If Repair Fails)

• Locate your most recent verified full backup using:

  RESTORE HEADERONLY FROM DISK = 'D:\Backups\YourDatabaseName.bak';

• Restore the database with NORECOVERY, then apply any differential or transaction log backups to minimize data loss.
• Switch the database recovery model temporarily to FULL after restoration to prevent future minimally-logged bulk operation issues:

  ALTER DATABASE YourDatabaseName SET RECOVERY FULL;

Step 5: Set Database Back to Multi-User Mode

• Run the following once repairs or restoration are complete:

  ALTER DATABASE YourDatabaseName SET MULTI_USER;

• Confirm no active bulk insert jobs or SSIS packages are queued to re-trigger the same corruption pattern.

Alternative or Verification Steps

• Re-run DBCC CHECKDB after remediation to confirm zero allocation errors are reported.
• If the error recurs, exclude SQL Server data/log file directories from third-party antivirus real-time scanning via Windows Security settings, as file-locking conflicts are a known trigger for this corruption pattern on Windows Server 2025.

Troubleshooting Guide: Error 5224 — Empty Large Object Page Could Not Be Deallocated

Why This Error Happens

This error occurs when SQL Server’s deallocation routine encounters a LOB (Large Object) page that is marked as empty but cannot be properly unlinked from the allocation structures (IAM or PFS pages) due to internal inconsistency. The root cause is typically database corruption resulting from an unexpected shutdown, storage subsystem failure, or a bad sector affecting the page allocation metadata. In some cases, this is triggered by antivirus or backup software locking the .mdf/.ndf files while SQL Server attempts to write to them.

Step-by-Step Resolution Guide

Step 1: Run DBCC CHECKDB to Assess Damage

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Review the output for allocation errors specifically tied to LOB pages (text, ntext, image, varchar(max), or XML data types).
• Note the exact page IDs and object IDs referenced in the error for targeted repair.

Step 2: Check Storage Hardware and Windows Event Logs

• Open Event Viewer on Windows Server 2025 / Windows 11 and navigate to Windows Logs > System.
• Look for Disk, NTFS, or Storport errors around the same timestamp as the SQL error.
• Verify the health of the underlying storage using Get-PhysicalDisk | Select FriendlyName, HealthStatus in PowerShell.
• If using virtualized storage (Hyper-V/VMware), confirm the virtual disk isn’t thin-provisioned and running out of space.

Step 3: Restore From a Known-Good Backup (Preferred Fix)

• Navigate to your backup repository (local path or network share, e.g., D:\SQLBackups\).
• Restore the database using RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\SQLBackups\YourDatabaseName.bak' WITH REPLACE;
• This avoids data loss associated with repair commands that may discard corrupted LOB pages entirely.

Step 4: Run Targeted Repair if No Backup Exists

• Set the database to single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Execute DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS); — note this is a last resort as it may remove the corrupted LOB rows entirely.
• Set the database back to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 5: Exclude SQL Server Files from Antivirus/Backup Scanning

• Open Windows Security or your third-party AV console.
• Add exclusions for the SQL Server data directory, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Exclude .mdf, .ldf, and .ndf file extensions from real-time scanning to prevent file-lock conflicts during write operations.

Alternative or Verification Steps

• Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; after remediation — a clean pass with zero allocation errors confirms the fix.
• If corruption persists after repair, consider migrating the affected table’s data to a new table using SELECT INTO and dropping the original, as this can isolate and eliminate damaged LOB page chains entirely.

Troubleshooting Guide: SQL Server Error 5225 – Ghost Record Cleanup Failure on LOB Pages

Why This Error Happens

Error 5225 occurs when SQL Server’s ghost cleanup task is unable to physically remove deleted (ghost) records from a Large Object (LOB) page because a long-running transaction or a query on a readable secondary replica still holds a reference to that version of the data. This is common in Always On Availability Group topologies where the ghost cleanup boundary on the primary is held back by the oldest active transaction on a readable secondary. It can also indicate underlying corruption on the LOB page itself if the condition persists even after all blocking transactions have closed.

Step-by-Step Resolution Guide

Step 1: Identify the Blocking Transaction or Replica

• Run SELECT * FROM sys.dm_hadr_database_replica_states on the primary to check the secondary_lag_seconds and last_hardened_time for each replica.
• On the secondary replica, execute DBCC OPENTRAN to identify any long-running read transactions holding snapshot isolation locks.
• Check sys.dm_exec_sessions and sys.dm_tran_active_snapshot_database_transactions on the secondary for sessions with an old transaction_sequence_num.

Step 2: Check the Current Ghost Cleanup Boundary

• Query SELECT * FROM sys.dm_db_log_stats(DB_ID()) to review log_truncation_holdup_reason; if it returns AVAILABILITY_REPLICA, this confirms the secondary is holding back cleanup.
• Use DBCC TRACEON(3604) followed by DBCC PAGE (in a test environment) with the file/page ID from the error message (%d:%d) to inspect the LOB page allocation unit.
• Cross-reference the oldest_active_transaction_id on the secondary using sys.dm_tran_database_transactions to correlate with the stalled boundary.

Step 3: Terminate or Resolve the Long-Running Secondary Query

• On the secondary replica, identify the offending SPID using sys.dm_exec_requests and cross-apply sys.dm_exec_sql_text(sql_handle).
• If the session is safe to terminate, run KILL <session_id> on the secondary to release the snapshot and unblock the ghost cleanup boundary on the primary.
• Restart the SQL Server Agent job “Ghost Cleanup” or wait for the automatic background task to run (default cycle: every 5–10 seconds) once the blocking session is cleared.

Step 4: Run Consistency Checks if the Error Persists

• Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS to rule out physical corruption on the LOB page referenced in the error (file ID : page ID).
• If corruption is confirmed, restore the affected database or table from the last known good backup rather than attempting REPAIR_ALLOW_DATA_LOSS on LOB pages, as this can cause data truncation.
• Apply the latest Cumulative Update for SQL Server 2025 via Windows Update or the Microsoft Update Catalog, as ghost cleanup timing bugs on AG secondaries are periodically patched.

Step 5: Adjust Secondary Replica Read Workload Settings

• On Windows Server 2025, open SQL Server Configuration Manager and verify the AG listener port isn’t conflicting with other services (check netstat -ano | findstr <port>).
• Reduce the isolation level burden on the secondary by setting shorter-lived read transactions, or adjust the application’s connection timeout in appsettings.json / connection string (Application Intent=ReadOnly).

Alternative or Verification Steps

• Verify the fix by re-running DBCC CHECKDB on the affected database and confirming Error 5225 no longer appears in the SQL Server Error Log (Get-Content "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG" in PowerShell on Windows Server 2025).
• As an alternative, if the issue is isolated to one readable secondary, temporarily remove it from the Availability Group read-routing list to confirm ghost cleanup completes normally on the primary before re-adding it.

Why This Error Happens

Error 5222 occurs when SQL Server’s deallocation routine encounters a page that references an allocation unit which has already been marked as dropped, typically due to metadata corruption in the system catalog views (sys.allocation_units) or the PFS/GAM/SGAM allocation bitmap pages. This usually results from an abrupt shutdown, storage-level I/O failure, or a torn page write that occurred mid-transaction during a DROP TABLE, TRUNCATE, or index rebuild operation. In most cases, this indicates physical corruption on disk that has desynchronized the page allocation structures from the actual data pages.

Step-by-Step Resolution Guide

Step 1: Take the Database Offline and Isolate the Corruption

• Immediately run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS to assess the full scope of corruption beyond just the 5222 error.
• Check the SQL Server Error Log (Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating I/O or disk subsystem warnings around the same timestamp.
• Note the exact Page ID (%d:%d) and Object ID reported in the error for targeted repair later.

Step 2: Verify Storage Integrity at the OS Level

• On Windows Server 2025 / Windows 11, open an elevated PowerShell and run Get-PhysicalDisk | Get-StorageReliabilityCounter to check for read/write errors or latency spikes on the underlying volume.
• Run chkdsk D:\ /f /r (replace D: with your data drive) during a maintenance window to repair file system-level inconsistencies.
• Confirm Windows Event Viewer (System log) has no Disk or Ntfs warning events (Event ID 7 or 55) coinciding with the SQL error.

Step 3: Restore from a Known Good Backup (Preferred Fix)

• Since allocation unit corruption is rarely safely repairable without data loss, restore the most recent full backup plus transaction logs up to the point before corruption began.
• Use RESTORE DATABASE YourDatabaseName FROM DISK = 'E:\Backups\YourDB_Full.bak' WITH NORECOVERY, followed by applicable log restores WITH RECOVERY.
• Validate backup integrity first using RESTORE VERIFYONLY FROM DISK = 'E:\Backups\YourDB_Full.bak'.

Step 4: Run Repair as a Last Resort (If No Valid Backup Exists)

• Set the database to single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
• Execute DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) — understand this may remove the orphaned pages/rows tied to the dropped allocation unit permanently.
• Revert to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER.

Alternative or Verification Steps

• After repair or restore, re-run DBCC CHECKDB with no repair options to confirm zero allocation errors are returned.
• If the error recurs on the same LUN/volume after storage validation, engage your SAN/storage vendor to rule out firmware-level write-caching or controller faults, as this is a common recurring root cause on Windows Server 2025 hosts using ReFS volumes.

Troubleshooting Guide: SQL Server Error 5223 – Empty Page Could Not Be Deallocated

Why This Error Happens

Error 5223 occurs when SQL Server’s internal deallocation routine encounters an empty page that does not match the expected state recorded in the Page Free Space (PFS) or Index Allocation Map (IAM) pages. This is almost always a symptom of underlying database corruption, often caused by a faulty I/O subsystem, improper shutdowns, or storage-level bit flips on the data file. In rarer cases, it can result from a SQL Server engine bug interacting with specific index maintenance operations (such as ALTER INDEX REORGANIZE) on Windows Server 2025 environments using newer storage drivers.

Step-by-Step Resolution Guide

Step 1: Verify the Scope of Corruption

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm if the error is isolated or part of broader corruption.
• Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating I/O warnings (Event ID 823 or 824).
• Cross-reference Windows Event Viewer (Application and System logs) for disk or storage controller warnings around the same timestamp.

Step 2: Isolate and Repair the Affected Object

• Identify the specific object ID and page ID referenced in the error using DBCC PAGE (DatabaseName, FileID, PageID, 3) WITH TABLERESULTS;.
• If the object is a non-clustered index, drop and rebuild it manually using DROP INDEX [IndexName] ON [TableName]; followed by CREATE INDEX... rather than relying on automated repair, as this avoids data loss.
• If the corruption resides in a heap or clustered index, proceed to restore from backup (preferred) before attempting REPAIR_ALLOW_DATA_LOSS.

Step 3: Restore From a Clean Backup (Recommended Path)

• Take a tail-log backup of the current state if the database is still accessible: BACKUP LOG [YourDatabaseName] TO DISK = 'E:\Backups\TailLog.trn';.
• Restore the last known good full backup followed by subsequent differential/log backups to bring the database to the point just before corruption.
• Validate the restored database immediately with DBCC CHECKDB before bringing it back online for production use.

Step 4: Check and Update Storage Drivers on Windows Server 2025

• Navigate to Device Manager > Storage Controllers and verify driver versions against the vendor’s latest release (especially for NVMe or SAN-attached volumes).
• Run wevtutil qe System /q:"*[System[(EventID=153 or EventID=129)]]" /f:text in PowerShell to check for disk timeout or controller reset events.
• Update firmware/drivers and reboot the Windows Server 2025 host to rule out a hardware-level root cause before re-running database operations.

Alternative or Verification Steps

• Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS; after remediation — a clean run with zero allocation or consistency errors confirms the fix.
• If corruption recurs on the same LUN or disk after repair, treat it as a hardware fault: migrate the database files to a different physical volume and run chkdsk E: /f /r (replace E: with your data drive) during a maintenance window.

Why This Error Happens

Error 5220 occurs when SQL Server attempts to relocate an Index Allocation Map (IAM) page during operations like ALTER INDEX ... REBUILD or DBCC SHRINKFILE, but the page cannot be moved due to underlying allocation corruption or a conflicting lock held by another process. This typically indicates that the database’s physical structure has inconsistencies, often caused by abrupt shutdowns, storage-level corruption, or hardware failures affecting the data file. It can also surface when shrink operations run concurrently with heavy write transactions, causing allocation contention on the IAM page.

Step-by-Step Resolution Guide

Step 1: Verify Database Integrity

• Open SQL Server Management Studio (SSMS) 21 or higher (compatible with SQL Server 2025).
• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to scan for allocation and consistency errors.
• Review the output for specific page IDs matching the one reported in Error 5220.

Step 2: Isolate and Halt Conflicting Processes

• Check for active shrink or rebuild operations using sys.dm_exec_requests to confirm no overlapping transactions are targeting the same file.
• Temporarily pause automated maintenance plans (via SQL Server Agent) that trigger index rebuilds or shrink operations.
• On Windows Server 2025, open Task Manager to ensure no third-party backup agents are locking the .mdf/.ndf files.

Step 3: Repair or Rebuild the Affected Index

• If DBCC CHECKDB confirms corruption, attempt ALTER INDEX [IndexName] ON [TableName] REBUILD WITH (ONLINE = ON); to force a clean rebuild of the allocation structure.
• If the index cannot rebuild online, take the database into single-user mode via ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE; before attempting repair.
• As a last resort, run DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) only after a verified backup exists, as this may discard corrupted rows.

Step 4: Validate Storage Health

• Navigate to Windows Server 2025 Event Viewer (Windows Logs > System) and check for Disk or NTFS-related warnings (Event ID 7 or 153) coinciding with the SQL error timestamp.
• Run chkdsk C: /f /r (replace with the correct volume) during a maintenance window to rule out underlying disk sector issues.

Alternative or Verification Steps

• Re-run the original operation (index rebuild or DBCC SHRINKFILE) that triggered Error 5220; if it completes without reporting the IAM page error, the fix was successful.
• If the error persists, restore the database from the last known good backup to a test environment and compare DBCC CHECKDB results to confirm the corruption originated in production rather than from a transient disk I/O issue.

Troubleshooting Guide: Error 5221 – IAM Page from Dropped Allocation Unit Could Not Be Moved

Why This Error Happens

This error occurs when SQL Server’s internal deallocation process attempts to move or reorganize an Index Allocation Map (IAM) page that belongs to an allocation unit already marked as dropped, typically during a DBCC SHRINKFILE or DBCC SHRINKDATABASE operation. The root cause is usually a race condition between the drop/deallocation of an object and the shrink process, or underlying corruption in the system metadata (sys.allocation_units, sys.partitions) that tracks page ownership. In some cases, this is triggered by concurrent DDL operations (like dropping tables or indexes) running simultaneously with a shrink operation on the same database file.

Step-by-Step Resolution Guide

Step 1: Halt Conflicting Operations and Check Database State

• Immediately stop any active DBCC SHRINKFILE or DBCC SHRINKDATABASE jobs running against the affected database.
• Check SQL Server Agent Job history and any scheduled maintenance plans for auto-shrink tasks: EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'default trace enabled';
• Verify no other sessions are issuing DDL (DROP TABLE, DROP INDEX) concurrently using sys.dm_exec_requests and sys.dm_tran_locks.

Step 2: Run CHECKDB to Identify Corruption

• Execute a full consistency check to confirm whether this is a transient issue or actual metadata corruption:
  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Review the output specifically for allocation errors related to IAM pages or orphaned allocation units.
• If corruption is confirmed, note the specific object IDs and page IDs reported for targeted repair.

Step 3: Disable Auto-Shrink and Retry in Isolation

• Disable auto-shrink on the database to prevent background shrink operations from colliding with DDL:
  ALTER DATABASE YourDatabaseName SET AUTO_SHRINK OFF;
• Place the database in single-user mode to eliminate concurrency during the retry:
  ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Re-run the shrink operation in isolation: DBCC SHRINKFILE (YourLogicalFileName, TargetSizeMB);
• Return the database to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 4: Apply Cumulative Updates and Patch Windows Server

• Confirm you are on the latest Cumulative Update (CU) for SQL Server 2025, as this error has known fixes in allocation-handling logic in early builds.
• On Windows Server 2025 / Windows 11, apply the latest Servicing Stack Update (SSU) via Windows Update or wusa.exe to ensure OS-level file system handling (NTFS/ReFS) is current, as file I/O anomalies can sometimes surface as allocation page errors.
• Restart the SQL Server service after patching: net stop MSSQLSERVER && net start MSSQLSERVER

Step 5: Repair or Restore from Backup if Corruption Persists

• If DBCC CHECKDB reports irreparable allocation errors, run the minimal repair level first:
  DBCC CHECKDB ('YourDatabaseName', REPAIR_REBUILD);
• If data loss is deemed acceptable only as a last resort: DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);
• If repair fails or the allocation unit is critical, restore the database from the last known good full backup plus transaction logs.

Alternative or Verification Steps

• Verify the fix by re-running DBCC SHRINKFILE on the affected file and confirming no 5221 errors appear in the SQL Server Error Log (EXEC sp_readerrorlog 0, 1, '5221';).
• As an alternative, if shrinking is not business-critical, avoid shrink operations entirely on this database and instead manage file growth through pre-sized data/log files to prevent recurrence.

Troubleshooting Guide: SQL Server Error 5218

Why This Error Happens

Error 5218 occurs when SQL Server’s internal heap page relocation process, typically triggered during an online index rebuild or ALTER TABLE operation, loses the metadata reference to the table name mid-operation. This is most commonly caused by catalog corruption in the system metadata tables, a schema change occurring concurrently with the heap reorganization, or tempdb contention disrupting the internal mapping between page IDs and object names. In rarer cases, this signals underlying disk-level corruption affecting the sys.objects or sys.partitions system catalog views.

Step-by-Step Resolution Guide

Step 1: Run a Full Consistency Check on the Database

• Open SQL Server Management Studio (SSMS) or sqlcmd and execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Review the output specifically for allocation errors or catalog corruption flagged against the heap table referenced in the error message.
• Note the exact %d:%d page ID pair from the original error so you can cross-reference it against the DBCC output.

Step 2: Identify and Isolate the Affected Table

• Query sys.allocation_units and sys.partitions to map the page ID back to an object_id, since the error indicates SQL Server itself failed to resolve this link:

  SELECT OBJECT_NAME(p.object_id), p.index_id, au.type_desc
  FROM sys.partitions p
  JOIN sys.allocation_units au ON p.partition_id = au.container_id
  WHERE au.allocation_unit_id = <value_from_error_log>;

• If the query returns NULL or no rows, this confirms orphaned metadata and validates the root cause.
• Check the SQL Server Error Log (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) for correlating 824 or 823 errors around the same timestamp.

Step 3: Rebuild the Heap Offline

• Stop any concurrent online operations against the database by checking sys.dm_exec_requests for blocking sessions.
• Take the table offline from concurrent writes and run the rebuild without the ONLINE = ON option:

  ALTER TABLE YourSchema.YourTable REBUILD WITH (ONLINE = OFF);

• This forces SQL Server to re-resolve the object metadata from scratch rather than relying on the cached mapping that failed.

Step 4: Restore System Catalog Integrity (If Corruption Persists)

• If DBCC CHECKDB in Step 1 reported catalog-level corruption, run DBCC CHECKCATALOG ('YourDatabaseName'); to isolate the issue to system tables specifically.
• If confirmed, restore the database from the last known-good backup rather than attempting REPAIR_ALLOW_DATA_LOSS, as catalog repairs on production systems carry a high risk of further object mapping loss.
• Validate backup integrity first using RESTORE VERIFYONLY FROM DISK = 'YourBackupPath.bak';

Alternative or Verification Steps

• After rebuilding, re-run DBCC CHECKDB with NO_INFOMSGS one final time to confirm zero allocation errors and that the previously orphaned page ID now resolves correctly.
• If the error recurs immediately after a clean rebuild, check Windows Server 2025 Event Viewer under Windows Logs > System for underlying storage/disk I/O errors (Event ID 7 or 153), as persistent hardware-level corruption can mimic this SQL-level symptom.

Troubleshooting Guide: SQL Server Error 5219 – Heap Page Could Not Be Moved

Why This Error Happens

Error 5219 occurs during a heap rebuild or data compaction operation (such as ALTER TABLE ... REBUILD or an index maintenance task) when SQL Server’s Storage Engine cannot relocate a specific heap page to consolidate space. This is typically caused by underlying physical corruption on the page, a conflicting lock held by another active transaction on that page, or an I/O subsystem failure that prevents the page from being read/written during the move operation. In rare cases, this is also triggered by disk-level corruption introduced by antivirus software or storage drivers actively scanning the MDF file during the operation.

Step-by-Step Resolution Guide

Step 1: Verify Database Consistency with DBCC CHECKDB

• Open SQL Server Management Studio (SSMS) or sqlcmd connected to the affected instance.
• Run the following command to identify the scope of corruption: DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Review the output for allocation errors or specific page IDs matching the %d:%d referenced in the original Error 5219 message.
• If corruption is confirmed, proceed to restore from a known-good backup rather than attempting a repair on a production system.

Step 2: Check for Active Locks or Blocking Transactions

• Query the Dynamic Management Views (DMVs) to rule out a locking conflict rather than physical corruption: SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID('YourDatabaseName');
• Identify if a long-running transaction is holding an exclusive (X) lock on the specific heap page or table.
• If blocking is found, kill the blocking session ID (SPID) using KILL <SPID>; and retry the heap rebuild operation.

Step 3: Isolate and Exclude Antivirus/Filter Driver Interference

• Navigate to Windows Security settings on your Windows Server 2025 / Windows 11 host.
• Go to Virus & Threat Protection > Manage Settings > Add or Remove Exclusions.
• Add exclusions for the SQL Server data directory, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Restart the SQL Server service via services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER in an elevated Command Prompt to clear any locked file handles.

Step 4: Rebuild the Heap Table Offline

• If the table is not actively corrupted but merely locked during the online operation, attempt an offline rebuild: ALTER TABLE YourTableName REBUILD WITH (ONLINE = OFF);
• Monitor the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for recurrence of Event ID 5219 during the rebuild.

Alternative or Verification Steps

• Verification: Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS; after the rebuild; a clean result with zero allocation errors confirms the heap page issue is resolved.
• Alternative: If physical corruption is confirmed via Step 1 and no backup is available, use DBCC CHECKTABLE (N'YourTableName', REPAIR_ALLOW_DATA_LOSS) as a last resort, understanding that this may permanently remove the corrupted row(s) from the heap.

Troubleshooting Guide: SQL Server Error 5217 — Empty Non-Leaf Level Index Page

Why This Error Happens

Error 5217 occurs when SQL Server’s online index rebuild or DBCC shrink/reorganize operation encounters an index B-tree page at a non-leaf level that contains no valid row entries, which violates expected index structure integrity. This typically stems from underlying index corruption caused by storage-level faults, improper shutdowns, or disk I/O errors that left the index metadata in an inconsistent state. It can also surface when a database file shrink operation attempts to relocate pages within a logically damaged index tree on Windows Server 2025 or Windows 11 hosted instances.

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 identify all affected objects.
• Note the object ID and index ID reported alongside Error 5217 for targeted repair.
• Check the SQL Server error log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating I/O or storage warnings.

Step 2: Rebuild the Affected Index

• Identify the specific index using sys.indexes and sys.objects joined on the object ID from the DBCC output.
• Run a full offline rebuild: ALTER INDEX [IndexName] ON [Schema].[TableName] REBUILD WITH (ONLINE = OFF, MAXDOP = 1);
• Avoid ONLINE = ON rebuilds initially, as the empty non-leaf page issue often reoccurs during online page-movement operations.

Step 3: Repair Database-Level Corruption if Rebuild Fails

• Put the database in single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Run DBCC CHECKDB ('YourDatabaseName', REPAIR_REBUILD); first, escalating to REPAIR_ALLOW_DATA_LOSS only if REPAIR_REBUILD does not resolve the page structure issue.
• Return the database to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 4: Validate Underlying Storage Health

• Check Windows Server 2025 Event Viewer under Windows Logs > System for Disk or NTFS warning events (Event ID 7, 51, or 153) that may indicate failing storage.
• Run chkdsk C: /f /r (adjust drive letter to your SQL data volume) during a maintenance window to rule out physical disk corruption.
• Verify antivirus or backup software is not locking .mdf/.ndf files during active I/O operations.

Step 5: Restore From Backup if Repair Is Unsuccessful

• If corruption persists after repair attempts, restore the database from the most recent clean full backup plus transaction log backups.
• Use RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabaseName.bak' WITH REPLACE, RECOVERY;

Alternative or Verification Steps

• Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS; after remediation — a clean run with zero allocation or consistency errors confirms the fix.
• If the error recurs only during shrink operations, avoid DBCC SHRINKFILE/SHRINKDATABASE on this database going forward, as frequent shrinking increases fragmentation and the likelihood of empty non-leaf page states.

Troubleshooting Guide: SQL Server Error 5215

Why This Error Happens

This error occurs when SQL Server’s internal page-moving operation (typically during a DBCC SHRINKFILE, ALTER INDEX REORGANIZE, or data file compaction process) encounters a page that belongs to a partition no longer present in the table’s current schema. This mismatch is most commonly caused by an orphaned or paused resumable index build that left metadata referencing a partition that has since been dropped or altered. SQL Server refuses to relocate the page because doing so could corrupt the index structure tied to the incomplete resumable operation.

Step-by-Step Resolution Guide

Step 1: Identify Active or Paused Resumable Index Operations

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to the affected instance.
• Run the following query to check for paused builds:

  SELECT * FROM sys.index_resumable_operations;

• Note the object_id, index_id, and last_partition_id values returned, as these pinpoint the conflicting resumable operation.

Step 2: Resume or Abort the Paused Index Build

• If the operation should be completed, resume it explicitly rather than letting it stay paused:

  ALTER INDEX [IndexName] ON [SchemaName].[TableName] RESUME;

• If the build is no longer needed (e.g., the partition was intentionally dropped), abort it to clear the orphaned metadata:

  ALTER INDEX [IndexName] ON [SchemaName].[TableName] ABORT;

• Confirm the row no longer appears in sys.index_resumable_operations after running either command.

Step 3: Re-run the Original Shrink or Maintenance Operation

• Once resumable operations are cleared, retry the operation that triggered Error 5215 (e.g., DBCC SHRINKFILE or index reorganize).
• On Windows Server 2025, ensure SQL Server Agent jobs tied to maintenance plans are temporarily disabled to prevent overlapping index operations during this retry:

  DBCC SHRINKFILE (N'YourDataFileName', 0);

• Monitor SQL Server Error Logs in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for recurrence of Error 5215 during the operation.

Step 4: Rebuild Indexes on Affected Tables (If Partition Was Dropped)

• If the partition was intentionally dropped, rebuild all indexes on the affected table to realign internal page mappings:

  ALTER INDEX ALL ON [SchemaName].[TableName] REBUILD;

• This ensures no residual references to the dropped partition remain in the storage engine metadata.

Alternative or Verification Steps

• Verify the fix by re-running SELECT * FROM sys.index_resumable_operations; — the query should return zero rows for the affected table, and the shrink/reorganize operation should complete without Error 5215.
• If the error persists, run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying corruption, and restore from the last known good backup if integrity errors are found.

Troubleshooting Guide: SQL Server Error 5216 – Heap Page Could Not Be Moved

Why This Error Happens

This error occurs when SQL Server’s background heap compaction or ghost cleanup process attempts to relocate a heap page, but the underlying table metadata has already been dropped or invalidated before the operation completed. It typically arises from a race condition between a DROP TABLE (or TRUNCATE) statement and an in-progress background maintenance task, such as deferred drop cleanup or heap defragmentation. This can also surface after abrupt transaction rollbacks or in tempdb when temporary objects are dropped while system threads still reference their page allocations.

Step-by-Step Resolution Guide

Step 1: Check SQL Server Error Log and System Health Session

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Look for correlating entries near Error 5216, especially references to DBCC CHECKDB, ghost cleanup, or deferred drop processes.
• Run the following query to inspect the system health extended events session:

  SELECT * FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL);

Step 2: Run DBCC CHECKDB to Validate Database Consistency

• Execute a full consistency check against the affected database:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Review output for allocation errors tied to the Page ID referenced in the error (%d:%d format = FileID:PageID).
• If corruption is confirmed, note whether it is isolated to a single heap or spans multiple objects.

Step 3: Clear Deferred Drop Queue and Rebuild Heap

• Force SQL Server to process any pending deferred drops by running a checkpoint and background task flush:

  CHECKPOINT;
  EXEC sp_configure 'background drop cleanup', 1;
  RECONFIGURE;

• If the table still exists but shows heap fragmentation issues, rebuild it explicitly:

  ALTER TABLE YourTableName REBUILD;

• For tempdb-related occurrences, restart the SQL Server service to clear residual tempdb heap allocations:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Apply Latest Cumulative Update for SQL Server 2025

• Verify your current build number:

  SELECT @@VERSION;

• Compare against the latest Cumulative Update (CU) listed on the Microsoft SQL Server release notes page.
• Install the latest CU via Windows Server 2025 Server Manager or directly through the standalone installer, then reboot the server.

Alternative or Verification Steps

• Re-run DBCC CHECKDB after applying fixes to confirm no further allocation errors reference the previously affected Page ID.
• If the error persists only in tempdb, schedule a full server restart during a maintenance window, as tempdb is rebuilt fresh on every SQL Server service restart, clearing any orphaned heap page references.

Troubleshooting Guide: SQL Server Error 5212 (SYSFILES1 Page Movement Restriction)

Why This Error Happens

This error occurs when SQL Server’s internal maintenance routines or a DBCC/SHRINKFILE operation attempt to relocate pages belonging to the SYSFILES1 system table away from the primary data file (.mdf). SQL Server enforces a strict architectural rule that system catalog pages—particularly file metadata stored in SYSFILES1—must always reside in the primary file of the database, as moving them would break the database’s ability to locate its own file structure during startup. This typically surfaces during a DBCC SHRINKFILE operation on the primary file when there isn’t enough contiguous free space elsewhere, or when the primary file has become fragmented or nearly full, forcing the engine to attempt (and fail) a system page relocation.

Step-by-Step Resolution Guide

Step 1: Verify Primary File Space and Growth Settings

• Open SQL Server Management Studio (SSMS) and run sp_helpfile against the affected database to confirm the current size and free space of the primary (.mdf) file.
• Check if autogrowth is disabled or capped too low under Database Properties > Files.
• Run the following query to check free space specifically in the primary file:

  SELECT name, size, FILEPROPERTY(name, 'SpaceUsed') AS SpaceUsed FROM sys.database_files;

Step 2: Avoid Shrinking the Primary File Directly

• If the error triggered during a shrink operation, cancel any DBCC SHRINKFILE or DBCC SHRINKDATABASE commands targeting the primary file.
• Instead, grow the primary file manually first to give SQL Server enough room to reorganize system pages internally:

  ALTER DATABASE [YourDB] MODIFY FILE (NAME = 'YourDB_Primary', SIZE = 500MB);

• Re-attempt the shrink operation only after confirming adequate free space (at least 20-25% headroom).

Step 3: Check for Primary File Corruption

• Run DBCC CHECKDB('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS to rule out underlying corruption in the primary file that might be interfering with metadata page allocation.
• Review the SQL Server error log (located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025/Windows 11 installs) for related I/O or allocation warnings preceding Error 5212.

Step 4: Rebuild the Primary Filegroup if Necessary

• If space and corruption checks pass but the error persists, consider creating a new filegroup, migrating user objects off the primary filegroup, and performing a clean shrink/rebuild cycle on the primary file.
• Ensure no user tables or indexes are explicitly bound to the PRIMARY filegroup aside from required system objects.

Alternative or Verification Steps

• After applying fixes, re-run the original shrink or maintenance operation and confirm Error 5212 no longer appears in the Windows Event Viewer (Application log) or SQL Server ERRORLOG.
• As a verification/alternative step, restore a recent full backup of the database to a test instance on Windows Server 2025 and attempt the same shrink/maintenance task to confirm the primary file was the actual bottleneck before applying changes to production.

Troubleshooting Guide: SQL Server Error 5213 — Page Could Not Be Moved During Shrink

Why This Error Happens

Error 5213 occurs when SQL Server’s shrink operation attempts to relocate a page toward the beginning of the data file, but another concurrent process (such as an allocation-related transaction, index rebuild, or DML operation) changes the ownership of that page before the move completes. This is typically a concurrency conflict rather than actual corruption, caused by running DBCC SHRINKFILE or DBCC SHRINKDATABASE while heavy transactional activity, auto-growth events, or parallel maintenance jobs are active on the same database. In rare cases, it can also surface due to underlying allocation page inconsistencies that require integrity verification.

Step-by-Step Resolution Guide

Step 1: Halt Conflicting Workloads Before Retrying Shrink

• Identify and pause scheduled jobs, ETL processes, or index maintenance plans running against the target database.
• Check sys.dm_exec_requests and sys.dm_tran_active_transactions for long-running or concurrent write transactions.
• Temporarily disable SQL Agent jobs tied to the database using SQL Server Management Studio (SSMS) or sp_update_job.

Step 2: Re-run the Shrink Operation in Isolation

• Execute the shrink during a low-activity maintenance window, ideally with single-user or restricted access mode:
• ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Run the shrink command again:
• DBCC SHRINKFILE (N'YourDataFile', TARGETSIZE_MB);
• Revert access mode once completed:
• ALTER DATABASE [YourDB] SET MULTI_USER;

Step 3: Validate Database Integrity

• Run a full consistency check to rule out underlying page corruption:
• DBCC CHECKDB (N'YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Review the output for allocation errors (Msg 8900-series) that might be triggering ownership instability.
• If corruption is found, follow standard repair procedures (REPAIR_REBUILD before considering REPAIR_ALLOW_DATA_LOSS).

Step 4: Adjust Shrink Strategy for Large Databases

• For very large tables, avoid shrinking the entire database at once; instead, shrink individual files incrementally:
• DBCC SHRINKFILE (N'YourDataFile', 10) WITH NOTRUNCATE;
• Consider rebuilding indexes afterward, since shrink operations fragment indexes heavily:
• ALTER INDEX ALL ON YourTable REBUILD;

Alternative or Verification Steps

• Confirm the fix by re-running DBCC SHRINKFILE and checking the SQL Server Error Log and sys.messages for recurrence of Error 5213.
• If the issue persists under normal low-traffic conditions, capture an Extended Events session filtering on page_allocated and databases_log_file_used_size_changed to pinpoint the exact process causing ownership churn during the shrink window.

Troubleshooting Guide: SQL Server Error 5214 – Page Could Not Be Moved During Shrink

Why This Error Happens

Error 5214 occurs when the DBCC SHRINKFILE or SHRINKDATABASE operation attempts to relocate a page toward the beginning of the file, but the page’s allocation type changes (e.g., from in-row data to another page type, or due to concurrent DDL/index rebuild activity) before the move completes. This is typically caused by concurrent transactional activity, parallel maintenance jobs (such as index reorganization or statistics updates), or heavy DML operations running simultaneously with the shrink process. In rare cases, it can also indicate underlying corruption or metadata inconsistency in the page allocation structures (PFS/GAM/SGAM pages).

Step-by-Step Resolution Guide

Step 1: Isolate the Database from Concurrent Operations

• Stop or pause any scheduled maintenance plans, ETL jobs, or index rebuild/reorganize tasks targeting the affected database.
• Check SQL Agent Jobs under SQL Server Agent > Jobs in SSMS to confirm nothing overlaps with the shrink window.
• Run sp_who2 or query sys.dm_exec_requests to identify active sessions writing to the database during shrink.

Step 2: Run CHECKDB to Validate Page Integrity

• Execute the following to rule out corruption as the root cause:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Review the output for allocation errors (Msg 8909, 8928, etc.) that may indicate PFS/GAM page corruption contributing to the shrink failure.
• If corruption is found, restore from a known good backup rather than attempting repair on a production system.

Step 3: Re-run Shrink in a Controlled, Single-User Context

• Set the database to restrict concurrent access before retrying:

  ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DBCC SHRINKFILE (YourLogicalFileName, TargetSizeMB);
  ALTER DATABASE YourDatabaseName SET MULTI_USER;

• This eliminates the race condition between page movement and concurrent page-type changes.

Step 4: Apply Latest Cumulative Update

• Confirm you are on the latest SQL Server 2025 Cumulative Update (CU), as shrink-engine race conditions around page type tracking are periodically patched.
• Check current build via:

  SELECT @@VERSION;

• Download and apply the latest CU from the Microsoft Update Catalog if your build is outdated.

Step 5: Rebuild Indexes Before Shrinking (Preventive)

• Run ALTER INDEX ALL ON [TableName] REBUILD; on heavily fragmented tables prior to shrink operations.
• This reduces the volume of page relocations required, lowering the chance of the type-change race condition.

Alternative or Verification Steps

• Verify the fix: Re-run DBCC SHRINKFILE during a maintenance window with no active sessions and confirm it completes without Error 5214 in the SQL Error Log (EXEC xp_readerrorlog 0, 1, N'5214';).
• Alternative approach: If the error persists, avoid shrinking live and instead perform a BACKUP → recreate database with correctly sized files → RESTORE data via SELECT INTO or BCP, which sidesteps the shrink/page-relocation mechanism entirely.

Why This Error Happens

Error 5210 occurs when SQL Server’s online file/page relocation process (such as DBCC SHRINKFILE with the MOVETO option or internal defragmentation routines) encounters a page type it does not recognize as movable, typically because the page is part of a corrupted allocation structure or belongs to a system object that cannot be relocated. This is most commonly caused by physical disk-level corruption, an interrupted I/O operation, or storage controller inconsistencies that have altered the page header metadata. It can also occur if the database was improperly shut down or if the underlying storage (SAN, NVMe, or virtual disk) silently dropped a write.

Step-by-Step Resolution Guide

Step 1: Run a Full Consistency Check

• Open SQL Server Management Studio (SSMS) or a sqlcmd session targeting the affected instance.
• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify the exact page and object affected.
• Review the output for the specific Page ID (%d:%d) referenced in Error 5210 to confirm which table or index is impacted.

Step 2: Validate Underlying Storage Health

• On Windows Server 2025 / Windows 11, open Event Viewer and check Windows Logs > System for Disk or NTFS warnings (Event ID 7, 11, or 51).
• Run chkdsk D: /f /r (replace D: with your SQL data drive) during a maintenance window to repair file system-level errors.
• If using virtualized or SAN storage, verify with your storage vendor’s diagnostic tool that no write-cache or controller errors occurred.

Step 3: Restore or Repair the Affected Database

• If a clean backup exists, restore the database using RESTORE DATABASE YourDatabaseName FROM DISK = 'E:\Backups\YourDatabaseName.bak' WITH REPLACE;
• If no backup is available, run DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) as a last resort, understanding that this may delete corrupted rows.
• Take the database offline temporarily using ALTER DATABASE YourDatabaseName SET OFFLINE; before performing repairs to prevent further writes to the corrupted page.

Step 4: Prevent Future Occurrences

• Enable CHECKSUM page verification by running ALTER DATABASE YourDatabaseName SET PAGE_VERIFY CHECKSUM; to catch corruption earlier.
• Schedule weekly DBCC CHECKDB jobs via SQL Server Agent to proactively detect page-level issues before they escalate.

Alternative or Verification Steps

• After remediation, re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; and confirm it returns 0 allocation errors and 0 consistency errors.
• If corruption persists after repair attempts, restore from the most recent verified backup instead of relying on REPAIR_ALLOW_DATA_LOSS, as repeated corruption often indicates a hardware-level failure requiring disk replacement.

Troubleshooting Guide: Error 5211 – Page Could Not Be Moved Because It Was Deallocated During Shrink

Why This Error Happens

This error occurs when SQL Server’s shrink operation (DBCC SHRINKDATABASE or DBCC SHRINKFILE) attempts to relocate a page to compact the database file, but the page is deallocated by a concurrent transaction before the move completes. This is typically caused by high concurrent write activity, index rebuilds, or batch deletes running simultaneously with the shrink process. The underlying issue is a race condition between the shrink thread and other sessions modifying the same data pages, which is more common in Windows Server 2025 environments running aggressive maintenance plans during active business hours.

Step-by-Step Resolution Guide

Step 1: Identify Concurrent Operations Causing Contention

• Open SQL Server Management Studio (SSMS) and query sys.dm_exec_requests to check for active sessions performing DELETE, UPDATE, or index maintenance operations.
• Run the following to check blocking sessions:

SELECT session_id, status, command, wait_type, blocking_session_id 
FROM sys.dm_exec_requests 
WHERE command LIKE '%SHRINK%';

• Note the database and table names involved in the conflict.

Step 2: Reschedule or Isolate the Shrink Operation

• Stop any active shrink job via SQL Server Agent (SQL Server Agent > Jobs) or cancel the running DBCC SHRINKFILE command.
• Schedule shrink operations during a maintenance window with minimal write activity, ideally using Task Scheduler on Windows Server 2025 to trigger a PowerShell script during off-peak hours.
• Example PowerShell invocation:

Invoke-Sqlcmd -Query "DBCC SHRINKFILE (N'YourDataFile', 10)" -ServerInstance "YourServer"

Step 3: Apply Latest Cumulative Update

• Navigate to the Microsoft Update Catalog and download the latest Cumulative Update (CU) for SQL Server 2025.
• Install the update via Setup.exe from the extracted CU package, ensuring you select “Database Engine Services” during the repair/update wizard.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Step 4: Run Shrink in Smaller Increments

• Instead of shrinking the entire file at once, use DBCC SHRINKFILE with a smaller target size increment (e.g., reduce by 500MB at a time rather than attempting a large reduction).
• This reduces the duration of the operation and lowers the chance of hitting deallocated pages mid-process.

Alternative or Verification Steps

• After re-running the shrink operation, check the SQL Server Error Log (Management > SQL Server Logs in SSMS) to confirm Error 5211 no longer appears.
• If the error persists, run DBCC CHECKDB on the affected database to rule out underlying corruption as a contributing factor.

Troubleshooting Guide: SQL Server Error 5207

Why This Error Happens

Error 5207 occurs when SQL Server’s internal defragmentation or shrink process attempts to relocate a page that belongs to a work table, which is a temporary internal structure used for intermediate query results (such as sorts, hash joins, or spools). Because work tables are transient and tied to the active execution of a query, they cannot be moved by operations like DBCC SHRINKFILE or DBCC SHRINKDATABASE while they are in use. This typically surfaces when a shrink operation runs concurrently with heavy query activity in tempdb, where most work tables reside.

Step-by-Step Resolution Guide

Step 1: Identify the Operation Triggering the Error

• Check the SQL Server error log for the full message context, including the database name and file ID associated with the page.
• Run SELECT * FROM sys.dm_exec_requests WHERE command LIKE 'DBCC%' to confirm if a shrink operation is currently active.
• Note whether the affected database is tempdb, since work tables almost always live there.

Step 2: Avoid Shrinking Databases/TempDB During Active Workloads

• Pause or reschedule any DBCC SHRINKFILE or DBCC SHRINKDATABASE jobs to a maintenance window with low query concurrency.
• Identify and temporarily suspend heavy batch jobs, ETL processes, or reporting queries that generate large sort/hash operations.
• Use sp_who2 or sys.dm_exec_sessions to identify sessions consuming significant tempdb space.

Step 3: Restart the Shrink Operation After Workload Quiesces

• Confirm no long-running queries are active using SELECT * FROM sys.dm_exec_requests WHERE status = 'running'.
• Re-issue the shrink command, e.g., DBCC SHRINKFILE (tempdev, 500).
• If the error persists, restart the SQL Server service during a scheduled downtime window to clear all work tables in tempdb, since they do not persist across restarts.

Step 4: Reconfigure TempDB Sizing to Reduce Future Shrink Needs

• Pre-size tempdb data and log files appropriately using ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 8192MB) to avoid frequent shrink/grow cycles.
• Distribute tempdb across multiple equally-sized data files (one per CPU core, up to 8) to reduce contention and fragmentation.
• Disable or limit scheduled shrink jobs in SQL Server Agent that run against tempdb.

Alternative or Verification Steps

• Verify the fix by re-running the shrink operation during a quiet period and confirming it completes without Error 5207 in the SQL Server error log.
• As an alternative, if shrinking tempdb is unavoidable during active hours, restart the SQL Server service instead—this instantly clears all work tables and resets tempdb to its configured size without requiring an online shrink.

Troubleshooting Guide: SQL Server Error 5208 — “Page could not be moved because it is a work file page”

Why This Error Happens

This error occurs when SQL Server’s auto-shrink or DBCC SHRINKFILE operation attempts to relocate a page in tempdb that is currently allocated to a work file (used for internal operations like hash joins, sorts, or spools). Because work file pages are transient and tied to an active session’s query execution, the shrink process cannot safely move them while they are in use. This is typically triggered by aggressive shrink operations running concurrently with heavy query workloads that rely on tempdb for intermediate result storage.

Step-by-Step Resolution Guide

Step 1: Identify and Halt Conflicting Shrink Operations

• Check if auto-shrink is enabled on tempdb or user databases by running: SELECT name, is_auto_shrink_on FROM sys.databases;
• If enabled, disable it immediately: ALTER DATABASE [YourDB] SET AUTO_SHRINK OFF;
• Cancel any manually running DBCC SHRINKFILE or DBCC SHRINKDATABASE commands currently in progress.

Step 2: Review and Reduce Tempdb Contention

• Query active work file usage: SELECT * FROM sys.dm_db_task_space_usage WHERE database_id = 2;
• Identify long-running queries with heavy sort/hash operations using SELECT * FROM sys.dm_exec_requests WHERE database_id = 2;
• Reschedule shrink operations to a maintenance window with minimal query activity.

Step 3: Resize Tempdb Files Manually Instead of Shrinking

• Navigate to SQL Server Configuration Manager or SSMS, right-click the instance, and go to Database Properties > Files.
• Instead of shrinking, pre-size tempdb data and log files appropriately (e.g., ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 8192MB);) to avoid frequent shrink/grow cycles.
• Restart the SQL Server service via services.msc on Windows Server 2025 for file size changes to take effect if pre-sizing was adjusted at startup.

Step 4: Apply Latest Cumulative Update

• Open Windows Update or use SQL Server Setup to check for the latest Cumulative Update (CU) for SQL Server 2025.
• Install any available CU, as work file allocation bugs during shrink operations are frequently patched in servicing releases.
• Reboot the server if prompted and confirm the build number via: SELECT @@VERSION;

Alternative or Verification Steps

• Verify the fix by re-running the shrink operation during off-peak hours and confirming no Error 5208 appears in the SQL Server Error Log (EXEC sp_readerrorlog;).
• If the issue persists, consider isolating tempdb onto dedicated, faster storage (SSD/NVMe) and increasing the number of tempdb data files to reduce contention, which often eliminates the root cause of work file conflicts.

Troubleshooting Guide: SQL Server Error 5209 – Dedicated Allocation Page Could Not Be Moved

Why This Error Happens

Error 5209 occurs when SQL Server’s DBCC SHRINKFILE or auto-shrink operation attempts to relocate a page that belongs to a dedicated allocation unit (such as an IAM page, GAM, SGAM, or PFS page) that cannot be physically moved within the data file. This typically happens due to internal database corruption, an interrupted shrink operation, or inconsistencies in the allocation bitmap pages caused by improper shutdowns or storage-level failures. In some cases, it also surfaces when third-party backup tools or antivirus software lock pages mid-operation, preventing SQL Server from safely reallocating them.

Step-by-Step Resolution Guide

Step 1: Verify Database Integrity First

• Open SQL Server Management Studio (SSMS) or use sqlcmd connected to the affected instance.
• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm whether corruption exists beyond the allocation page issue.
• Review the output log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for related 824 or 5180 errors that often accompany 5209.

Step 2: Stop Any Active Shrink Operations

• Check for running shrink jobs via sys.dm_exec_requests filtering on command = 'DbccFilesCompact'.
• Cancel the session using KILL <session_id>; if an active shrink is stuck mid-transaction.
• Avoid scheduling auto-shrink on databases with heavy allocation churn; disable it via ALTER DATABASE YourDatabaseName SET AUTO_SHRINK OFF;.

Step 3: Rebuild Indexes to Clear Fragmented Allocation Units

• Execute ALTER INDEX ALL ON YourTableName REBUILD WITH (ONLINE = ON); on tables residing in the affected filegroup.
• This forces SQL Server to reallocate extents, often resolving orphaned dedicated allocation page references.
• If the database is in SIMPLE recovery model, ensure sufficient log space is pre-allocated to avoid autogrowth interruptions during rebuild.

Step 4: Restore From a Known Good Backup (If Corruption Confirmed)

• If DBCC CHECKDB reports unrepairable errors tied to allocation pages, restore the database from the last verified full backup.
• Use RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabase.bak' WITH REPLACE, RECOVERY;.
• Apply subsequent transaction log backups to minimize data loss before resuming shrink operations.

Step 5: Validate Storage and File System Health

• On Windows Server 2025/Windows 11, run chkdsk D: /f /r (replace D: with the volume hosting your .mdf/.ldf files) during a maintenance window.
• Check Event Viewer under Windows Logs > System for Disk or NTFS warning events coinciding with the SQL error timestamp.
• Confirm storage drivers and firmware for SAN/NAS-attached volumes are current, as outdated drivers can cause silent page-level write failures.

Alternative or Verification Steps

• After remediation, re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS; and confirm zero allocation errors are returned before re-enabling shrink operations.
• If error 5209 persists despite a clean CHECKDB result, attempt the shrink operation during off-peak hours using DBCC SHRINKFILE (N'YourDataFile', TRUNCATEONLY) instead of a full shrink, as this avoids moving dedicated allocation pages entirely.

Troubleshooting Guide: SQL Server Error 5205 — “Moving page %d:%d failed”

Why This Error Happens

Error 5205 occurs when SQL Server’s DBCC SHRINKFILE or auto-shrink operation attempts to relocate allocated pages from the end of a data file but encounters a page it cannot move, typically due to page corruption, an active transaction holding a lock on that page, or an unmovable allocation unit (such as a forwarded row in a heap, LOB data, or a page belonging to a system object). This is also commonly triggered by insufficient free space in the target location within the file or by index fragmentation that prevents the shrink engine from relocating extent pages cleanly. In some cases, underlying disk I/O errors or a corrupted database page map (GAM/SGAM/PFS pages) cause the page-move operation to fail outright.

Step-by-Step Resolution Guide

Step 1: Check Database Integrity First

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out corruption.
• Review the SQL Server error log (Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for accompanying 823, 824, or 825 errors indicating disk-level corruption.
• If corruption is found, restore from a known-good backup rather than proceeding with shrink operations.

Step 2: Identify and Resolve Blocking Transactions

• Run sp_whoisactive or query sys.dm_tran_locks to check for long-running transactions holding locks on the file being shrunk.
• Kill or wait out blocking sessions using KILL <session_id>; only after confirming it is safe to terminate.
• Avoid running shrink operations during peak transaction periods or active ETL jobs.

Step 3: Rebuild Indexes and Heaps Before Shrinking

• Run ALTER INDEX ALL ON YourTableName REBUILD WITH (ONLINE = ON); (Enterprise/Online capable editions) to eliminate forwarded records and fragmented heap pages.
• For heaps specifically, forwarded pointers are a known cause of page-move failures — rebuild the heap using ALTER TABLE YourTableName REBUILD;.
• Re-attempt the shrink operation with DBCC SHRINKFILE (N'YourLogicalFileName', TRUNCATEONLY); first to avoid unnecessary page movement.

Step 4: Retry Shrink in Smaller Increments

• Instead of a single large shrink, run DBCC SHRINKFILE (N'YourLogicalFileName', <target_size_in_MB>) in smaller incremental steps.
• Monitor tempdb and transaction log space usage during the operation, since insufficient space can also trigger page-move failures.
• If using Windows Server 2025 storage tiers (ReFS or Storage Spaces Direct), confirm the volume isn’t throttling I/O during the operation via Performance Monitor (PhysicalDisk counters).

Step 5: Update SQL Server and Verify File System Health

• Apply the latest Cumulative Update for SQL Server 2025 from Microsoft Update Catalog, as page-move logic in shrink operations has known fixes in recent builds.
• Run chkdsk C: /f /r (or the relevant drive hosting your .mdf/.ndf files) on Windows Server 2025 to rule out NTFS/ReFS file system corruption.
• Confirm the SQL Server service account has full read/write permissions on the data file directory via File Properties > Security > Advanced.

Alternative or Verification Steps

• After applying fixes, re-run DBCC SHRINKFILE during an off-peak maintenance window and confirm no Error 5205 appears in the error log or sys.messages output.
• If the issue persists, use DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS only as a last resort in a non-production environment, and consider migrating the affected table’s data to a new file group instead of shrinking in place.

Troubleshooting Guide: SQL Server Error 5206

Why This Error Happens

Error 5206 occurs when SQL Server’s shrink or page-move operation attempts to relocate a data page but fails because the page cannot be read from disk. This is typically caused by underlying storage corruption, a failing disk subsystem, or I/O errors reported by the Windows storage stack (e.g., bad sectors, controller faults, or file system inconsistencies). It can also result from the database file being in a damaged state due to improper shutdowns, antivirus interference, or hardware-level disk failures on Windows Server 2025 / Windows 11.

Step-by-Step Resolution Guide

Step 1: Check the SQL Server Error Log and Windows Event Viewer

• Open SQL Server Management Studio (SSMS) and run EXEC sp_readerrorlog; to review surrounding messages near Error 5206.
• Open Windows Event Viewer (eventvwr.msc) and inspect the System and Application logs under Windows Logs for related Disk, NTFS, or I/O warnings (Event IDs 7, 9, 11, 51).
• Note the exact file ID and page number (%d:%d) referenced in the error for targeted repair.

Step 2: Run DBCC CHECKDB to Assess Corruption

• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; in SSMS.
• Review output for allocation or consistency errors tied to the same page ID from the 5206 error.
• If corruption is confirmed, avoid running further shrink operations until the issue is resolved, as this can worsen data loss.

Step 3: Restore from a Clean Backup (Preferred Fix)

• Identify the most recent full backup taken before corruption occurred, typically stored under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup\.
• Restore using: RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\Backup\YourDatabase.bak' WITH REPLACE, RECOVERY;
• Validate restored database integrity immediately with DBCC CHECKDB again.

Step 4: Run Hardware and File System Diagnostics

• Use chkdsk D: /f /r (replace D: with the actual data drive) in an elevated Command Prompt to repair file system-level errors.
• Check disk health using Get-PhysicalDisk | Get-StorageReliabilityCounter in PowerShell to detect read/write error counts or predictive failure flags.
• Replace or remap failing storage hardware if SMART data indicates degradation.

Step 5: Repair as a Last Resort (Data Loss Risk)

• If no clean backup exists, run DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) only after setting the database to single-user mode via ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;.
• Revert to multi-user mode afterward: ALTER DATABASE YourDatabaseName SET MULTI_USER;.

Alternative or Verification Steps

• Re-run DBCC CHECKDB after remediation to confirm zero allocation or consistency errors are reported.
• If errors persist, engage Microsoft Support or a storage vendor to perform a deep SAN/RAID controller diagnostic, as Error 5206 at the hardware level often indicates failing physical media beyond software-level repair.

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.

Troubleshooting Guide: SQL Server Error 5203 – DBCC SHRINKFILE Waiting for Snapshot Transaction

Why This Error Happens

This error occurs because DBCC SHRINKFILE cannot relocate or deallocate pages while an active snapshot transaction (under Snapshot Isolation or Read Committed Snapshot Isolation) still holds a reference to the version store at an older timestamp. SQL Server must preserve row versions for any transaction that began before the shrink operation, so the shrink process stalls until those long-running or orphaned transactions commit or roll back. This is commonly caused by long-idle sessions, uncommitted transactions left open by applications, or linked connections sharing the same transaction sequence number (XSN) that never terminates.

Step-by-Step Resolution Guide

Step 1: Identify the Blocking Snapshot Transactions

• Run the following query to find active snapshot transactions and their timestamps:

SELECT session_id, transaction_id, elapsed_time_seconds, first_snapshot_sequence_num 
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

• Cross-reference session_id with sys.dm_exec_sessions to identify the originating application, host, or login.
• Note the first_snapshot_sequence_num matching the timestamp referenced in the Error 5203 message.

Step 2: Terminate or Resolve the Offending Session

• If the session is idle or abandoned, safely kill it using:

KILL <session_id>;

• If the session belongs to an application with an open transaction, coordinate with the application team to force a COMMIT or ROLLBACK rather than killing it abruptly, especially in production environments.
• Check for orphaned distributed transactions using sys.dm_tran_active_transactions if the session is tied to MSDTC.

Step 3: Re-run DBCC SHRINKFILE After Clearing Transactions

• Confirm no active snapshot transactions remain by re-querying sys.dm_tran_active_snapshot_database_transactions.
• Execute the shrink operation again:

DBCC SHRINKFILE (N'YourFileLogicalName', TRUNCATEONLY);

• If the error persists, check sys.databases for the snapshot_isolation_state_desc and is_read_committed_snapshot_on settings, as these dictate how aggressively SQL Server retains versioning data.

Step 4: Adjust Long-Running Transaction Timeouts (Optional)

• On Windows Server 2025 hosts with heavy OLTP loads, review application connection pooling settings to prevent transactions from remaining open indefinitely.
• Consider setting a LOCK_TIMEOUT or implementing application-level transaction timeout logic to avoid recurrence.

Alternative or Verification Steps

• Verify the fix by re-running DBCC SHRINKFILE and confirming it completes without Error 5203; also check sys.dm_db_file_space_usage to confirm space was reclaimed.
• If the issue recurs frequently, consider disabling Snapshot Isolation temporarily during maintenance windows via ALTER DATABASE [YourDB] SET ALLOW_SNAPSHOT_ISOLATION OFF; (only after confirming no dependent processes require it), then re-enable it post-shrink.

Troubleshooting Guide: SQL Server Error 5204 — “Could not find allocation unit ID %I64d”

Why This Error Happens

This error occurs when SQL Server’s internal metadata tracking for allocation units becomes inconsistent with the actual data pages stored in the database, typically due to database corruption caused by improper shutdowns, storage subsystem failures, or disk-level bit rot. It can also surface after a failed or interrupted index rebuild, a hardware-level I/O error that silently corrupted system catalog tables, or antivirus/backup software locking database files mid-write. In nearly all cases, this points to physical corruption in the sys.allocation_units system table or the data/index pages it references, rather than a permissions or configuration issue.

Step-by-Step Resolution Guide

Step 1: Run DBCC CHECKDB to Confirm and Scope the Corruption

• Open SQL Server Management Studio (SSMS) or sqlcmd on Windows Server 2025 / Windows 11.
• Execute: DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Review the output for specific object IDs, page IDs, and allocation unit IDs flagged as corrupt.
• Check the SQL Server error log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating I/O or storage errors around the same timestamp.

Step 2: Restore From a Known-Good Backup (Preferred Fix)

• Identify the most recent clean full backup using: RESTORE HEADERONLY FROM DISK = 'D:\Backups\YourDatabase.bak';
• Restore to a new database name first to validate integrity: RESTORE DATABASE YourDatabaseName_Test FROM DISK = 'D:\Backups\YourDatabase.bak' WITH MOVE 'logical_name' TO 'D:\Data\YourDatabase_Test.mdf', MOVE 'logical_name_log' TO 'D:\Data\YourDatabase_Test.ldf';
• Run DBCC CHECKDB against the restored copy to confirm it is error-free before cutting over production traffic.

Step 3: Use Repair Options as a Last Resort

• Set the database to single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Run the least destructive option first: DBCC CHECKDB ('YourDatabaseName', REPAIR_REBUILD);
• If allocation errors persist, escalate to: DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS); — note this may delete corrupted rows/pages permanently.
• Return the database to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 4: Check Underlying Storage Health

• On Windows Server 2025, run chkdsk D: /F /R (adjust drive letter) during a maintenance window to rule out disk-level file system errors.
• Review Windows Event Viewer under Windows Logs > System for Event IDs 7, 11, or 51 (disk/controller errors) that may indicate failing hardware.
• Confirm your storage driver and firmware are up to date via Windows Update or the vendor’s management utility.

Alternative or Verification Steps

• After repair or restore, re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; and confirm zero allocation or consistency errors are returned.
• If corruption recurs frequently, consider running DBCC CHECKDB on a scheduled Agent job and enabling Page Verification (CHECKSUM) via ALTER DATABASE YourDatabaseName SET PAGE_VERIFY CHECKSUM; to catch future I/O corruption earlier.

Troubleshooting Guide: SQL Server Error 5199 — Raw Device Path Not Supported in Failover Clustered Environment

Why This Error Happens

This error occurs because SQL Server 2025, when running in a Failover Cluster Instance (FCI) configuration, explicitly disallows the use of raw device paths (such as \\.\PhysicalDriveX or unformatted partition references) for database file storage. Raw devices bypass the Windows file system entirely, which prevents SQL Server’s cluster resource DLL from properly validating disk ownership, performing I/O fencing, and executing failover health checks. Since clustered instances rely on NTFS/ReFS volume paths registered as cluster shared disks or CSV mount points to coordinate ownership between nodes, any attempt to specify a raw device path during database creation, attachment, or restore operations is rejected at validation time.

Step-by-Step Resolution Guide

Step 1: Identify the Raw Device Reference

• Check the exact T-SQL statement that triggered the error (CREATE DATABASE, ALTER DATABASE, RESTORE DATABASE, or sp_attach_db).
• Look for file paths formatted like \\.\PhysicalDrive1 or similar low-level device identifiers instead of a drive letter or mount path.
• Run SELECT name, physical_name FROM sys.master_files; to confirm whether any existing file entries reference raw devices.

Step 2: Reconfigure Storage as a Clustered Disk Resource

• Open Failover Cluster Manager on Windows Server 2025 and navigate to Storage > Disks.
• Ensure the target disk is already added as a clustered disk resource (right-click Disks > Add Disk if missing).
• Format the disk with NTFS or ReFS and assign it a drive letter (e.g., E:\) or mount it as a folder under an existing clustered volume (e.g., E:\SQLData\).
• Avoid exposing the disk as a raw partition; it must appear under Available Storage with a recognized file system.

Step 3: Update the Database File Path in SQL Server

• Modify the CREATE DATABASE or ALTER DATABASE ... MODIFY FILE statement to reference the new NTFS/ReFS path instead of the raw device:

  ALTER DATABASE [YourDB] MODIFY FILE 
  (NAME = YourDB_Data, FILENAME = 'E:\SQLData\YourDB.mdf');

• If this occurs during installation or an FCI role configuration, revisit the SQL Server Failover Cluster Installation Wizard and point the data directory field to the clustered volume path, not a raw disk.

Step 4: Validate Cluster Resource Dependencies

• In Failover Cluster Manager, open the SQL Server role properties and confirm the disk resource is listed under Dependencies for the SQL Server Database Engine resource.
• Use PowerShell to confirm cluster disk status:

  Get-ClusterResource | Where-Object {$_.ResourceType -eq "Physical Disk"}

• Ensure the resource state is Online and owned by the active node before retrying the database operation.

Alternative or Verification Steps

• Run RESTORE FILELISTONLY FROM DISK = 'E:\Backup\YourDB.bak'; to confirm the target paths resolve to valid NTFS/ReFS locations before executing the full restore.
• If raw device usage was intentional for performance testing, use a standalone (non-clustered) SQL Server instance instead, as raw device support is only restricted within FCI deployments — not standalone installations.

Troubleshooting Guide: SQL Server Error 5201 – DBCC SHRINKDATABASE File Skipped

Why This Error Happens

This error occurs because the data file SQL Server is attempting to shrink has little to no free space available to reclaim — the allocated pages are already packed near the end of the file, preventing SQL Server from releasing space back to the OS. It is not a corruption issue but rather a logical constraint: SQL Server cannot move allocated extents past the current end-of-file marker during the shrink operation. This commonly happens on databases with high fragmentation, active transactions holding locks on pages near the file end, or when the target size specified is smaller than the actual data footprint.

Step-by-Step Resolution Guide

Step 1: Check Actual Space Usage and File Layout

• Run sp_spaceused against the target database to view reserved vs. unused space.
• Execute the following to inspect per-file space details:

  SELECT name, size, FILEPROPERTY(name, 'SpaceUsed') AS SpaceUsedPages
  FROM sys.database_files;

• Confirm whether the “unused space” is actually enough to justify a shrink in the first place.

Step 2: Rebuild Indexes to Consolidate Free Space

• Fragmented indexes near the end of the file prevent page relocation during shrink.
• Run a full index rebuild to compact data toward the front of the file:

  ALTER INDEX ALL ON [YourTableName] REBUILD;

• For large databases, rebuild indexes table-by-table during a maintenance window to avoid log growth issues.
• Re-run DBCC SHRINKDATABASE or DBCC SHRINKFILE afterward.

Step 3: Use DBCC SHRINKFILE with TRUNCATEONLY or Target Size

• Instead of shrinking the whole database, target the specific file ID reported in the error.
• Run:

  DBCC SHRINKFILE (N'YourLogicalFileName', TRUNCATEONLY);

• If that fails, try specifying a realistic target size rather than 0:

  DBCC SHRINKFILE (N'YourLogicalFileName', 500);

• Verify no long-running transactions or open snapshots are holding locks on the file (sys.dm_tran_active_transactions).

Step 4: Check for Blocking Sessions or Replication/CDC Locks

• Open SQL Server Management Studio (SSMS) and query active sessions:

  SELECT * FROM sys.dm_exec_requests WHERE database_id = DB_ID('YourDB');

• Temporarily disable Change Data Capture (CDC) or replication if it’s pinning log/data pages during the shrink attempt.
• Retry the shrink operation after confirming no blocking transactions remain.

Alternative or Verification Steps

• After rebuilding indexes and shrinking, re-check file size with sys.database_files and confirm the “File ID %d” from the original error is no longer flagged in the SQL Server Error Log.
• If shrinking consistently fails, consider leaving the file at its current size (shrinking is often not recommended for performance reasons) or migrate data to a new filegroup and drop the original file entirely.

Why This Error Happens

This message appears when SQL Server detects that a database file residing on an NTFS-compressed folder or volume has been automatically decompressed by the engine. SQL Server does not support running data or log files on NTFS compression because it conflicts with the internal page allocation and I/O patterns, so the engine silently strips the compression attribute to protect data integrity. While technically informational rather than fatal, it often signals that an administrator or script enabled compression on a directory that later became a SQL Server file location, causing unexpected disk I/O overhead until corrected.

Step-by-Step Resolution Guide

Step 1: Identify the Affected File and Path

• Check the SQL Server Error Log or Event Viewer (Application log, Event ID 5196) to capture the exact file path referenced in the message.
• Run the following query to cross-reference the file with its database:

  SELECT name, physical_name FROM sys.master_files;

Step 2: Verify NTFS Compression State

• Open File Explorer, navigate to the folder in question (e.g., D:\SQLData\MSSQL\DATA), right-click the file or folder, and select Properties > Advanced.
• Confirm whether “Compress contents to save disk space” is checked.
• Alternatively, use PowerShell to check compression state in bulk:

  Get-ChildItem "D:\SQLData\MSSQL\DATA" | ForEach-Object { 
      $attr = (Get-Item $_.FullName).Attributes 
      "$($_.Name): $attr" 
  }

Step 3: Remove Compression at the Folder/Volume Level

• Uncheck compression via Properties > Advanced in File Explorer, applying the change to the folder and all subfiles.
• Or disable it via command line for the entire directory tree:

  compact /u /s:D:\SQLData\MSSQL\DATA

• Ensure no backup, maintenance, or deployment scripts re-enable compression on this path (check Scheduled Tasks and DBA automation scripts).

Step 4: Restart SQL Server and Reattach/Validate Database

• Restart the SQL Server (MSSQLSERVER) service via SQL Server Configuration Manager to clear any cached I/O handles.
• Run DBCC CHECKDB against the affected database to confirm no corruption occurred during the compression/decompression cycle:

  DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS;

Step 5: Move Files to a Non-Compressed Volume (If Required)

• If the underlying volume itself is compressed at the file system level (not just the folder), migrate the data/log files to a dedicated NTFS volume without compression enabled.
• Use ALTER DATABASE ... MODIFY FILE to update the logical file path after physically moving the file offline.

Alternative or Verification Steps

• Re-run Get-ChildItem or check folder Properties > Advanced again to confirm the compression attribute no longer appears on the data/log files.
• Monitor the SQL Server Error Log for 24–48 hours to ensure Event ID 5196 does not reoccur; if it does, audit Group Policy or storage provisioning scripts that may be re-applying NTFS compression automatically at the volume level.

Troubleshooting Guide: SQL Server Error 5197 – Uncompress File Failure

Why This Error Happens

Error 5197 occurs when SQL Server 2025 attempts to decompress a backup file (typically a .bak file created with COMPRESSION enabled) but encounters corruption, truncation, or an I/O mismatch during the decompression stream. This is commonly caused by the backup file being copied incompletely (e.g., FTP in ASCII mode instead of binary), antivirus software locking or modifying the file mid-read, or the underlying storage media (disk/network share) returning bad sectors or dropped packets. It can also occur if the backup file was created on a newer build of SQL Server with a compression algorithm not fully compatible with the restoring instance.

Step-by-Step Resolution Guide

Step 1: Verify File Integrity and Transfer Method

• Check the file size of the .bak file on the source server versus the destination server using Get-Item <path> | Select-Object Length in PowerShell.
• If the file was moved via FTP, re-transfer it using Binary mode, not ASCII, as ASCII mode corrupts compressed binary streams.
• If copied over SMB/network share, re-copy using Robocopy with the /Z (restartable mode) and /V (verbose) switches to ensure a byte-for-byte copy:

  robocopy "\\source\share" "C:\Backups" filename.bak /Z /V

Step 2: Exclude SQL Server Directories from Antivirus Scanning

• Open Windows Security on Windows Server 2025 and navigate to Virus & threat protection > Manage settings > Exclusions.
• Add exclusions for the SQL Server data and backup directories, typically:
• C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup
• C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA
• Restart the SQL Server (MSSQLSERVER) service after applying exclusions:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 3: Test Restore with CHECKSUM and NO_COMPRESSION Fallback

• Run a validation pass using RESTORE VERIFYONLY with checksum validation to isolate whether the file itself is corrupted:

  RESTORE VERIFYONLY 
  FROM DISK = 'C:\Backups\filename.bak' 
  WITH CHECKSUM;

• If this fails, the compressed stream is damaged; request a fresh backup from the source system rather than attempting repair.
• If you control the backup job, regenerate it using WITH CHECKSUM, COMPRESSION to catch corruption at creation time going forward:

  BACKUP DATABASE [YourDB] 
  TO DISK = 'C:\Backups\filename.bak' 
  WITH COMPRESSION, CHECKSUM;

Step 4: Check Disk Health and Available Space

• Confirm the destination drive has sufficient free space (decompression requires temporary working space equal to or greater than the uncompressed database size).
• Run chkdsk C: /scan on Windows Server 2025 to rule out underlying disk corruption affecting the read operation.
• Review the Windows Event Viewer > System log for Disk or NTFS warning events (Event ID 7 or 153) that coincide with the timestamp of the SQL error.

Alternative or Verification Steps

• Verify the fix: Re-run RESTORE DATABASE [YourDB] FROM DISK = 'C:\Backups\filename.bak' WITH CHECKSUM; and confirm it completes without Error 5197, then check sys.databases to confirm the database state is ONLINE.
• Alternative: If corruption persists only on this specific file, use RESTORE HEADERONLY to confirm the backup set is readable at the metadata level — if this also fails, the file must be regenerated from source rather than repaired.

Troubleshooting Guide: Error 5198 – UNC Path Not Supported in Failover Clustered Environment

Why This Error Happens

This error occurs because SQL Server 2025, when running in a Windows Server Failover Cluster (WSFC) configuration, does not allow database files (data, log, or filestream) to be created or attached directly from a UNC path (\\servername\share) in certain contexts, particularly when the resource lacks proper cluster-aware validation. This typically happens when a DBA attempts to run CREATE DATABASE, ALTER DATABASE, or RESTORE commands pointing to a network share without registering that share as a valid dependent cluster resource. Additionally, this can occur if the File Share Witness or Scale-Out File Server configuration is improperly mapped to the SQL Server role in Failover Cluster Manager.

Step-by-Step Resolution Guide

Step 1: Verify the Current Path Configuration

• Check the exact T-SQL command that triggered Error 5198 (e.g., CREATE DATABASE... ON (FILENAME = '\\FileServer\Share\data.mdf')).
• Confirm if the target path is a standard UNC path or a Cluster Shared Volume (CSV) mistakenly formatted as UNC.
• Run Get-ClusterResource in PowerShell on Windows Server 2025 to see if the file share is currently recognized by the cluster.

Step 2: Register the File Share as a Cluster Resource

• Open Failover Cluster Manager on Windows Server 2025.
• Navigate to Storage > Disks and ensure the UNC path corresponds to a Scale-Out File Server (SOFS) or a properly clustered file share resource.
• If using a standard file share, add it via PowerShell: Add-ClusterSharedVolume -Name "Cluster Disk 1".
• Ensure the SQL Server service account has explicit Full Control NTFS and Share permissions on the target UNC folder.

Step 3: Update SQL Server Trace Flags and Registry Permissions

• In some Windows Server 2025 cluster configurations, SQL Server blocks UNC paths unless Trace Flag 1807 is enabled, which permits UNC path usage for database files.
• Open SQL Server Configuration Manager > right-click the SQL Server Service > Properties > Startup Parameters.
• Add the parameter: -T1807.
• Restart the SQL Server service for the trace flag to take effect.

Step 4: Re-validate the Cluster Role Dependency

• Go back to Failover Cluster Manager, select the SQL Server role, and click Add Storage or Add Resource > File Share.
• Ensure the UNC path is listed as a Dependency for the SQL Server resource group; if it is missing, right-click the SQL Server resource, select Properties > Dependencies, and add the file share resource.
• Bring the resource group Online to apply the dependency chain.

Alternative or Verification Steps

• To verify the fix, re-run the original CREATE DATABASE or RESTORE command pointing to the UNC path; if successful, the database will mount without throwing Error 5198.
• As an alternative, if cluster dependency configuration is restricted by security policy, migrate the database files to a local Cluster Shared Volume (CSV) path (e.g., C:\ClusterStorage\Volume1\MSSQL\Data) instead of relying on an external UNC share.

Troubleshooting Guide: SQL Server Error 5193 — Lease Mismatch File Access Failure

Why This Error Happens

Error 5193 occurs when SQL Server 2025 detects a mismatch between the lease token it holds for a database file and the lease token recorded by the underlying storage layer, typically on network-attached storage, Storage Spaces Direct, or cloud-backed volumes. This usually happens when another process or node has taken ownership of the file lease (common in failover cluster or Always On scenarios), or when the lease expired due to a network interruption or storage latency spike. SQL Server treats this as a potential data integrity risk and proactively takes the database offline rather than risk writing to a file it no longer has exclusive, verified control over.

Step-by-Step Resolution Guide

Step 1: Verify Storage Connectivity and Lease Ownership

• Check Windows Server 2025 Event Viewer under Windows Logs > System for disk or network timeout events (Event ID 153 or 157) that may have preceded the SQL error.
• Run Get-SmbMultichannelConnection or Get-PhysicalDisk in PowerShell to confirm storage path stability if using SMB3 or Storage Spaces Direct.
• Confirm no other SQL Server instance, cluster node, or snapshot/backup tool currently holds an active lease on the same .mdf/.ldf file path.

Step 2: Restart the Affected Database Safely

• Open SQL Server Management Studio (SSMS) or run via sqlcmd:
• ALTER DATABASE [YourDBName] SET OFFLINE WITH ROLLBACK IMMEDIATE;
• ALTER DATABASE [YourDBName] SET ONLINE;
• This forces SQL Server to release and re-acquire a fresh lease token against the storage subsystem.

Step 3: Check Cluster and Always On Availability Group Configuration

• If using Always On AGs or Failover Cluster Instances (FCI), open Failover Cluster Manager and verify only one node is actively hosting the resource group.
• Inspect %ProgramFiles%\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for concurrent lease acquisition attempts from a secondary node.
• Ensure cluster heartbeat network settings (under Networks tab in Failover Cluster Manager) have no latency exceeding the configured SameSubnetThreshold.

Step 4: Update Storage Drivers and SQL Server Cumulative Update

• Confirm the storage HBA/NIC drivers are current via Device Manager > Properties > Driver tab, especially for SMB Direct (RDMA) adapters.
• Apply the latest SQL Server 2025 Cumulative Update from the Microsoft Update Catalog, as early builds may contain known lease-handling timing bugs.
• Reboot the node after driver and CU installation to clear any stale lease cache held by the OS storage driver.

Alternative or Verification Steps

• After bringing the database back online, run DBCC CHECKDB('YourDBName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm no corruption occurred during the lease failure window.
• If the issue recurs intermittently, move the database files to local SSD storage temporarily to isolate whether the root cause is network/storage-layer latency versus a SQL Server configuration problem.

Troubleshooting Guide: Error 5194 — FILESTREAM Log File Size Must Be Greater Than or Equal to 1 MB

Why This Error Happens

This error occurs when SQL Server 2025 attempts to create, attach, or restore a FILESTREAM-enabled database, but the log file size specified for the FILESTREAM container is set below the required 1 MB minimum threshold. This typically happens due to a misconfigured database creation script, an incorrectly edited model database template, or a manual override in the file growth settings that sets the initial size to 0 KB or a value under 1024 KB. It can also surface during database migration or restore operations where the original FILESTREAM log file metadata was corrupted or improperly specified in the T-SQL command.

Step-by-Step Resolution Guide

Step 1: Review the T-SQL Script or Command Causing the Error

• Locate the CREATE DATABASE or ALTER DATABASE statement responsible for defining the FILESTREAM filegroup.
• Check the SIZE parameter under the FILESTREAM file specification; ensure it is explicitly set to 1MB or higher (e.g., SIZE = 1MB).
• Example of a corrected script snippet:

  ALTER DATABASE [YourDB]
  ADD FILE (NAME = FileStreamLog, FILENAME = 'C:\SQLData\FileStreamLog')
  TO FILEGROUP [FileStreamGroup]

• Confirm no automation tool or PowerShell deployment script is passing a size value of 0 or a null parameter.

Step 2: Verify FILESTREAM Configuration in SQL Server Configuration Manager

• Open SQL Server Configuration Manager on Windows Server 2025 / Windows 11.
• Navigate to SQL Server Services > right-click your instance > Properties > FILESTREAM tab.
• Ensure Enable FILESTREAM for Transact-SQL access is checked.
• Confirm the Windows share name is valid and matches the path referenced in your database file structure.

Step 3: Inspect and Repair the Underlying File System Path

• Navigate to the physical FILESTREAM data directory (e.g., C:\SQLData\FileStreamLog).
• Verify the folder is not read-only, corrupted, or redirected to a network share with insufficient permissions.
• If the directory was manually created, delete it and allow SQL Server to regenerate it automatically during the next CREATE DATABASE attempt.
• Check NTFS permissions to ensure the SQL Server service account has Full Control on the FILESTREAM data path.

Step 4: Recreate the Database Using Correct Size Parameters

• Drop the problematic database (if it is not yet in production) using:

  DROP DATABASE [YourDB]

• Rerun the CREATE DATABASE statement with the FILESTREAM log size explicitly defined as 1MB or greater.
• If working with a restore operation, edit the backup metadata script to correct the SIZE value before executing RESTORE DATABASE.

Step 5: Apply the Latest Cumulative Update for SQL Server 2025

• Check Windows Update or the Microsoft Update Catalog for the latest SQL Server 2025 Cumulative Update (CU).
• Install the update, as known FILESTREAM metadata validation bugs are often patched in CU releases.
• Restart the SQL Server service after installation via SQL Server Configuration Manager.

Alternative or Verification Steps

• Run SELECT * FROM sys.master_files WHERE type_desc = 'FILESTREAM' to confirm the file size and path are correctly registered without errors.
• If the issue persists, use SQL Server Management Studio (SSMS) to manually create the database through the GUI, explicitly setting the FILESTREAM log file size field to 1 MB or higher before executing the script.

Troubleshooting Guide: SQL Server Error 5195 (Cluster Service Path Verification Failure)

Why This Error Happens

Error 5195 occurs when SQL Server’s Cluster Service attempts to validate a file path (typically for a database, log file, or FILESTREAM container) that resides on a cluster shared disk or Cluster Shared Volume (CSV), but the call fails due to a permissions mismatch, a disconnected/offline cluster resource, or an invalid path reference. This is commonly triggered when the SQL Server service account lacks sufficient rights on the clustered storage, when the dependent disk resource is not online in Failover Cluster Manager, or when the path uses a drive letter/mount point that doesn’t match the cluster resource configuration. In Windows Server 2025 environments, this can also surface due to stricter CSV ownership validation or a corrupted cluster database entry.

Step-by-Step Resolution Guide

Step 1: Verify Cluster Resource Status and Ownership

• Open Failover Cluster Manager and navigate to Storage > Disks.
• Confirm the disk resource associated with the SQL Server instance is Online and owned by the correct node.
• If offline, right-click the resource and select Bring Online; check the resource’s Dependencies tab to ensure it’s correctly linked to the SQL Server role.

Step 2: Check SQL Server Service Account Permissions

• Open services.msc and confirm the SQL Server service is running under the correct domain/virtual account (e.g., NT SERVICE\MSSQLSERVER or a dedicated service account).
• Navigate to the target file path (e.g., E:\SQLData\ or the CSV path like C:\ClusterStorage\Volume1\MSSQL\DATA) and verify the service account has Full Control via Properties > Security.
• Run the following in an elevated PowerShell session to confirm access:

Test-Path "C:\ClusterStorage\Volume1\MSSQL\DATA" 
Get-Acl "C:\ClusterStorage\Volume1\MSSQL\DATA" | Format-List

Step 3: Validate the Cluster Configuration

• Run the built-in Validate Cluster Configuration wizard from Failover Cluster Manager to detect storage or network inconsistencies.
• Check for stale or orphaned registry entries under:

HKEY_LOCAL_MACHINE\Cluster\Resources

• If a path was recently changed (e.g., drive letter reassignment), update the SQL Server resource dependencies to reflect the new path using:

Get-ClusterResource "SQL Server" | Get-ClusterParameter

Step 4: Restart the Cluster Service and SQL Server Resource

• Restart the Cluster Service (ClusSvc) on the affected node via PowerShell:

Restart-Service -Name ClusSvc -Force

• Take the SQL Server role offline and bring it back online through Failover Cluster Manager to force re-verification of the file path.

Alternative or Verification Steps

• After completing the steps above, check the Cluster Events log and SQL Server Error Log to confirm Error 5195 no longer appears during service startup or failover testing.
• As an alternative, if the path was moved or renamed, use sp_detach_db and sp_attach_db (or ALTER DATABASE … MODIFY FILE) to manually repoint SQL Server to the correct, verified cluster storage path.

Troubleshooting Guide: Error 5190 – Checkpoint Failure on Transactional File System Resource Manager (SQL Server 2025)

Why This Error Happens

Error 5190 occurs when SQL Server 2025, leveraging the Windows Transactional NTFS (TxF) subsystem, fails to checkpoint the resource manager (RM) responsible for tracking filesystem transaction logs, typically due to I/O failures, disk corruption, or insufficient permissions on the $TxfLog directory. This can also happen if the underlying volume has TxF disabled, is running low on disk space, or if third-party antivirus/backup software is locking the transaction log files during the checkpoint operation. Since Windows Server 2025 and Windows 11 have deprecated and restricted TxF functionality by default, SQL Server instances relying on FILESTREAM or FileTable features are particularly susceptible to this NT status failure.

Step-by-Step Resolution Guide

Step 1: Verify Transactional NTFS (TxF) Status on the Volume

• Open an elevated Command Prompt and run fsutil resource info <VolumeLetter>: to check if TxF is active on the drive hosting your SQL Server data files.
• Confirm the resource manager GUID is valid and not marked as corrupted or unavailable.
• If TxF is disabled (common on fresh Windows Server 2025/Windows 11 builds), you will need to re-enable it or migrate away from FILESTREAM/FileTable features that depend on it.

Step 2: Check Disk Space and Health on the Hosting Volume

• Run chkdsk <VolumeLetter>: /f /r during a maintenance window to repair underlying NTFS structural issues.
• Ensure at least 15-20% free space remains on the volume, as TxF checkpointing requires buffer space for the $TxfLog system file.
• Review Event Viewer under Windows Logs > System for any corresponding Disk or Ntfs warnings (Event ID 55 or 140) that correlate with the SQL Server error timestamp.

Step 3: Audit and Correct NTFS Permissions on the Resource Manager

• Navigate to the root of the affected volume and use icacls <VolumeLetter>:\$Extend\$RmMetadata /T to verify the SQL Server service account has Read/Write access.
• Grant the SQL Server service account (e.g., NT Service\MSSQLSERVER) full control over the $TxfLog and $TxfLogContainer system files if access is denied.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to reinitialize the resource manager handle.

Step 4: Exclude SQL Server Files from Antivirus and Backup Agents

• Add exclusions for the SQL Server data directory (e.g., D:\SQLData\) and the $TxfLog system folder in your antivirus console.
• Disable “real-time scanning” temporarily to test if a third-party agent is locking the file handle during the checkpoint cycle.

Alternative or Verification Steps

• Verify the Fix: Monitor the SQL Server Error Log and Windows Event Viewer for 24-48 hours to confirm Error 5190 does not recur during automatic checkpoint intervals.
• Alternative Solution: If TxF remains unstable due to Microsoft’s deprecation roadmap, migrate FILESTREAM data to a standard VARBINARY(MAX) column or utilize FileTable alternatives that do not rely on the legacy Transactional NTFS driver, as this feature is slated for full removal in future Windows builds.

Troubleshooting Guide: SQL Server Error 5191 – Local Directory Used for TempDB in a Clustered Server

Why This Error Happens

This error occurs when SQL Server 2025 is configured as a clustered instance (Failover Cluster Instance) and the tempdb data or log files are pointed to a local, non-shared directory path that does not exist identically on every node in the Windows Server 2025 cluster. Because failover clustering requires that any node be able to host the SQL Server resource at any time, a path that only exists on one node (or lacks proper permissions) causes validation to fail. This typically stems from manually editing file paths during setup, restoring a configuration from a standalone instance, or forgetting to pre-create the local folder structure on all passive nodes.

Step-by-Step Resolution Guide

Step 1: Verify TempDB Path Consistency Across All Nodes

• Open File Explorer or PowerShell on every node participating in the Windows Server Failover Cluster (WSFC).
• Confirm the exact folder path referenced in the error message (e.g., D:\SQLData\TempDB) exists identically on each node.
• Run Test-Path "D:\SQLData\TempDB" in PowerShell on each node to confirm True is returned.

Step 2: Create the Missing Directory on All Cluster Nodes

• If the path does not exist on one or more nodes, manually create it using New-Item -ItemType Directory -Path "D:\SQLData\TempDB" -Force.
• Ensure the drive letter and folder structure match exactly across nodes (local disks only, not cluster shared storage, since tempdb is intentionally placed on local storage in clustered setups).

Step 3: Assign Correct SQL Server Service Permissions

• Open Computer Management > Local Users and Groups or use icacls to grant the SQL Server service account full control.
• Run: icacls "D:\SQLData\TempDB" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F" (replace with the correct service account or named instance service).
• Repeat this permission grant on every node, since local security permissions do not replicate across the cluster automatically.

Step 4: Update TempDB Configuration via SQL Server Configuration Manager

• Open SQL Server Configuration Manager and navigate to the instance’s Startup Parameters, or use ALTER DATABASE tempdb MODIFY FILE in SQL Server Management Studio.
• Set the correct local path that now exists on all nodes:
• ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\SQLData\TempDB\tempdb.mdf');
• Restart the SQL Server service or fail the resource over to apply changes.

Step 5: Validate Cluster Role Failover

• Use Failover Cluster Manager to manually move the SQL Server role to each node.
• Confirm the SQL Server service starts successfully on every node without re-triggering Error 5191.

Alternative or Verification Steps

• Check the SQL Server Error Log and Windows Event Viewer (Application log) after failover to confirm no further Event ID 5191 entries appear.
• As an alternative, use a SQL Server startup trace flag or the -T parameter temporarily to redirect tempdb to a guaranteed-valid local path (e.g., C:\TEMP) on all nodes while permissions and directory structures are being corrected.

Troubleshooting Guide: SQL Server Error 5192 – Maximum Allowed File Size Exceeded

Why This Error Happens

Error 5192 occurs when a database file (data or log) attempts to grow beyond the maximum size limit defined either by the file’s own MAXSIZE property or by an underlying constraint such as disk volume capacity or file system limitations (e.g., FAT32’s 4GB cap). This is common when autogrowth settings push a file toward a hardcoded ceiling, or when a file is restricted to a fixed size during creation and transactional activity has filled it completely. In virtualized or containerized Windows Server 2025 environments, this can also surface if the underlying VHDX or disk partition itself has less free space than the file’s configured maximum.

Step-by-Step Resolution Guide

Step 1: Identify the Affected File and Current Size Limit

• Open SQL Server Management Studio (SSMS) and run the following query to check file properties:

  SELECT name, size, max_size, growth, physical_name 
  FROM sys.master_files 
  WHERE database_id = DB_ID('YourDatabaseName');

• Note the max_size value (in 8KB pages) — a value of -1 means unlimited, while any positive number indicates a hard cap.
• Cross-reference the physical file path to confirm which disk volume it resides on in Windows Server 2025.

Step 2: Modify the File’s MAXSIZE Property

• Open a new query window in SSMS and increase or remove the size restriction:

  ALTER DATABASE YourDatabaseName 
  MODIFY FILE (NAME = YourLogicalFileName, MAXSIZE = UNLIMITED);

• Alternatively, set a specific larger cap instead of UNLIMITED:

  ALTER DATABASE YourDatabaseName 
  MODIFY FILE (NAME = YourLogicalFileName, MAXSIZE = 102400MB);

• Ensure the target disk has sufficient free space to accommodate this new ceiling.

Step 3: Check Underlying Disk and File System Constraints

• Open Disk Management (diskmgmt.msc) on Windows Server 2025 and verify the volume hosting the .mdf/.ldf files is formatted as NTFS or ReFS (not FAT32).
• Confirm there is adequate unallocated space on the physical or virtual disk; extend the VHDX or partition if necessary using:

  Resize-Partition -DriveLetter D -Size (Get-PartitionSupportedSize -DriveLetter D).SizeMax

• Restart the SQL Server service via services.msc if any file-growth settings required a service-level refresh.

Step 4: Review Autogrowth Settings to Prevent Recurrence

• Right-click the database in SSMS, go to Properties > Files, and set a reasonable fixed-size autogrowth increment (e.g., 512MB) instead of a percentage-based growth.
• Avoid setting MAXSIZE too close to current disk capacity to leave headroom for future growth.

Alternative or Verification Steps

• Re-run the sys.master_files query from Step 1 to confirm max_size now reflects the updated value, then trigger a test transaction to ensure the file grows without throwing Error 5192.
• If the error persists, check Windows Event Viewer (Applications and Services Logs > SQL Server) for correlating disk I/O or storage-level errors that may indicate a SAN/VHDX capacity issue overriding the SQL Server configuration.

Why This Error Happens

Error 5188 occurs when SQL Server’s Transactional NTFS (TxF) integration attempts to replay (redo) operations against a File System Resource Manager (FSRM) during database recovery, but the underlying NTFS transaction log or marshaled data has become corrupted or inaccessible. This is commonly triggered by abrupt power loss, storage controller failures, or disk corruption that occurred while FILESTREAM or FileTable transactions were in flight. Since Windows Server 2025 deprecates and restricts legacy TxF functionality, this error can also surface when TxF is disabled at the OS level while SQL Server still expects it for FILESTREAM recovery.

Step-by-Step Resolution Guide

Step 1: Verify TxF Status on Windows Server 2025 / Windows 11

• Open an elevated PowerShell session and run: fsutil resource info C:
• Confirm whether the Transactional Resource Manager (RM) is present and online for the volume hosting your FILESTREAM data.
• If TxF is disabled (common in hardened Windows Server 2025 builds), re-enable it temporarily using: fsutil transaction ktmutil start

Step 2: Inspect and Repair the KTM (Kernel Transaction Manager) Log

• Locate the resource manager folder, typically hidden under \System Volume Information\{GUID} on the affected volume.
• Run a full chkdsk pass to repair underlying NTFS metadata corruption: chkdsk D: /f /r (replace D: with your FILESTREAM data volume).
• Reboot the server to allow Windows to complete the repair pass before SQL Server restarts recovery.

Step 3: Rebuild the FILESTREAM/FileTable Resource Manager

• Take the affected database offline: ALTER DATABASE [YourDB] SET OFFLINE;
• Remove and recreate the FILESTREAM filegroup container if corruption persists, pointing to a fresh NTFS volume with TxF explicitly enabled.
• Bring the database back online and allow SQL Server to complete redo: ALTER DATABASE [YourDB] SET ONLINE;

Step 4: Restore From a Known-Good Backup (If Redo Still Fails)

• If error 5188 persists after repair attempts, restore the database from the last verified full backup plus transaction log chain.
• Use RESTORE DATABASE [YourDB] WITH RECOVERY only after confirming log backups are uncorrupted via RESTORE VERIFYONLY.

Alternative or Verification Steps

• Check the SQL Server error log and Windows Event Viewer (Application log) for subsequent Event ID 5188 recurrences after the fix; a clean startup with no FILESTREAM recovery warnings confirms resolution.
• If the issue reoccurs on the same volume, migrate the FILESTREAM filegroup to a different physical disk, as this strongly indicates underlying storage hardware degradation rather than a software-level NTFS transaction issue.

Troubleshooting Guide: SQL Server Error 5189 – TxF Resource Manager Undo Failure

Why This Error Happens

This error occurs when SQL Server 2025 attempts to roll back or recover a transaction using the Transactional NTFS (TxF) Resource Manager, but the underlying NT status code indicates the operation failed. This is most commonly caused by a corrupted or inaccessible TxF log on the volume hosting the database files, insufficient permissions for the SQL Server service account on the Resource Manager directory, or the TxF feature being disabled/deprecated on Windows Server 2025. Since Microsoft has deprecated TxF in recent Windows builds, filestream or FILETABLE-based databases relying on it may encounter undo failures during crash recovery.

Step-by-Step Resolution Guide

Step 1: Verify TxF Feature Status on the Host OS

• Open PowerShell as Administrator and run: Get-WindowsOptionalFeature -Online -FeatureName TFC-TxF
• Confirm the feature state is “Enabled.” If disabled, this explains the undo failure since TxF is deprecated and may be partially removed in Windows Server 2025 builds.
• If disabled, consider migrating away from FILESTREAM/FILETABLE features that depend on TxF, as re-enabling may not be supported long-term.

Step 2: Check Resource Manager Folder Permissions

• Navigate to the FILESTREAM data container path, typically found under your database’s filegroup directory (e.g., C:\SQLData\MyDB_FileStreamData\).
• Right-click the folder, go to Properties > Security, and ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Run icacls "C:\SQLData\MyDB_FileStreamData" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F" to reset permissions if needed.

Step 3: Repair or Rebuild the Transactional Resource Manager Log

• Stop the SQL Server service via services.msc or net stop MSSQLSERVER.
• Locate the hidden $TxfLog folder on the root of the volume hosting your database files (e.g., D:\$TxfLog).
• If corruption is suspected, run chkdsk D: /f /r to repair the NTFS volume and its transactional log structures, then restart the SQL Server service.

Step 4: Restore the Database from a Clean Backup

• If the error persists after repair attempts, the FILESTREAM container may be unrecoverable.
• Restore the affected database from the last known good full backup plus transaction logs using RESTORE DATABASE MyDB FROM DISK = 'E:\Backups\MyDB.bak' WITH NORECOVERY, followed by applying log backups.
• Bring the database online with RESTORE DATABASE MyDB WITH RECOVERY.

Alternative or Verification Steps

• After applying fixes, run DBCC CHECKDB('MyDB') WITH NO_INFOMSGS, ALL_ERRORMSGS to confirm no further corruption exists and that the database comes online cleanly.
• As a long-term alternative, migrate FILESTREAM data to standard BLOB storage or Azure Blob Storage integration, since TxF dependency is being phased out and will continue causing instability on newer Windows Server releases.

Troubleshooting Guide: SQL Server Error 5184 – Clustered File Resource Dependency Failure

Why This Error Happens

Error 5184 occurs when SQL Server 2025, running as a clustered instance, attempts to create or access a database file located on a disk that is not registered as a dependency resource of the SQL Server cluster role. This typically happens during database creation, file moves, or restores where the target path points to a volume outside the cluster group’s ownership, or the physical disk resource has failed to come online in Failover Cluster Manager. Additionally, this can surface if Storage Spaces Direct (S2D) volumes or CSV (Cluster Shared Volumes) paths are misconfigured and not properly associated with the SQL Server resource dependency chain in Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify Disk Ownership in Failover Cluster Manager

• Open Failover Cluster Manager on the active node.
• Navigate to the specific Role hosting your SQL Server instance.
• Check the Disks tab to confirm the target volume (e.g., E:\ or F:\) is listed as Online and owned by the current node.
• If the disk is missing or offline, right-click it and select Bring Online.

Step 2: Add the Disk as a Dependency Resource

• In Failover Cluster Manager, right-click the SQL Server resource (not the Network Name) under the Role.
• Select Properties, then navigate to the Dependencies tab.
• Click Insert and add the specific disk resource (e.g., “Cluster Disk 3”) that contains the path referenced in the error.
• Click Apply and OK, ensuring no circular dependency errors are thrown.

Step 3: Correct the File Path in T-SQL

• Verify the exact path syntax used in your CREATE DATABASE or ALTER DATABASE command.
• Ensure the path matches the mount point exactly as seen in Disk Management (e.g., D:\MSSQL\Data\ vs. a nested mount point that isn’t clustered).
• Re-run the query:

  ALTER DATABASE [YourDB] MODIFY FILE (NAME = YourDB_dat, FILENAME = 'D:\MSSQL\Data\YourDB.mdf');

Step 4: Restart the SQL Server Resource

• Take the SQL Server resource offline via Failover Cluster Manager.
• Bring the resource back online to force it to re-read the updated dependency tree.
• Monitor the Cluster Events log for any immediate re-triggering of Error 5184.

Alternative or Verification Steps

• Verification: Attempt a test failover of the SQL Server role to a secondary node; if the disk and database move successfully without manual intervention, the dependency is correctly configured.
• Alternative: If the disk cannot be added as a cluster resource (e.g., it is a local, non-shared disk), move the database files to a Cluster Shared Volume (CSV) or a dedicated clustered disk resource instead of a local server path.

Troubleshooting Guide: Error 5185 – FILESTREAM Log File Mismatch

Why This Error Happens

Error 5185 occurs when SQL Server 2025 attempts to attach or recover a database and discovers that the FILESTREAM data container on disk does not contain a matching filestream.hdr log record, or the container’s internal GUID does not align with what is registered in the primary database files. This typically happens after an improper file copy (using Windows Explorer instead of a proper backup/restore), a failed or interrupted detach operation, or because the FILESTREAM container was moved/restored without its accompanying transaction log metadata. It can also surface when antivirus or backup software locks or modifies files inside the FILESTREAM container folder, corrupting the internal RSRC/log stream structure.

Step-by-Step Resolution Guide

Step 1: Verify the FILESTREAM Container Structure

• Navigate to the FILESTREAM data container path (e.g., D:\SQLData\FileStreamContainer\).
• Confirm the presence of the hidden filestream.hdr file and the $FSLOG folder.
• Use dir /a in Command Prompt to reveal hidden system files, since Windows Explorer may hide these by default.
• If either filestream.hdr or $FSLOG is missing or has a size of 0 KB, the container is corrupted and cannot be auto-repaired.

Step 2: Detach and Properly Reattach the Database

• Run EXEC sp_detach_db 'YourDatabaseName', 'true'; in SQL Server Management Studio (SSMS) to cleanly detach without losing FILESTREAM pointers.
• Move the entire database folder (not just the .mdf/.ldf) as a single unit to the new location if relocating.
• Reattach using CREATE DATABASE YourDatabaseName ON (FILENAME = 'D:\SQLData\YourDB.mdf'), (FILENAME = 'D:\SQLData\YourDB_log.ldf'), (FILENAME = 'D:\SQLData\FileStreamContainer') FOR ATTACH;

Step 3: Restore From a Valid Backup Instead of Manual Copy

• If the container is confirmed corrupted (Step 1), do not attempt manual repair; restore the full database and FILESTREAM filegroup from the last known good .bak file.
• Use RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDB.bak' WITH REPLACE, RECOVERY;
• Ensure the RESTORE statement includes both the FILESTREAM filegroup and standard filegroups in a single operation.

Step 4: Check Antivirus and Backup Agent Exclusions

• Open Windows Security settings or your third-party antivirus console on Windows Server 2025.
• Add an exclusion rule for the entire FILESTREAM container directory path to prevent real-time scanning from locking or altering the $FSLOG stream during write operations.
• Restart the SQL Server service (net stop MSSQLSERVER && net start MSSQLSERVER) after applying exclusions.

Alternative or Verification Steps

• Run DBCC CHECKDB('YourDatabaseName') WITH DATA_PURITY; after reattaching to confirm no further consistency errors are reported.
• If the container remains unrecoverable, use ALTER DATABASE YourDatabaseName REMOVE FILE FileStreamGroupName; to drop the broken filegroup and recreate it fresh, then reload FILESTREAM data from your application-layer backup source.

Troubleshooting Guide: SQL Server Error 5186 – TxF Resource Manager Startup Failure

Why This Error Happens

Error 5186 occurs when SQL Server 2025 attempts to initialize the Transactional File System (TxF) Resource Manager on a database that relies on filestream or filetable functionality, but the underlying NTFS volume cannot process the transaction log correctly. This is typically caused by the TxF feature being disabled at the OS level (common in newer Windows Server 2025 / Windows 11 builds where TxF is deprecated), corrupted transaction metadata in the $TxfLog system directory, or insufficient permissions for the SQL Server service account on the volume hosting the database files. The NT status code accompanying the error usually points to STATUS_OBJECT_NAME_NOT_FOUND or STATUS_ACCESS_DENIED, confirming either a missing resource manager log or a security context mismatch.

Step-by-Step Resolution Guide

Step 1: Verify TxF Status on the Volume

• Open an elevated Command Prompt and run fsutil resource info C: (replace C: with the drive hosting your database files) to check if a resource manager already exists.
• Confirm TxF is not disabled by checking fsutil behavior query DisableTxfAndTm — a return value of 1 means TxF is disabled system-wide.
• If disabled, re-enable temporarily using fsutil behavior set DisableTxfAndTm 0, then restart the server.

Step 2: Check and Repair NTFS Volume Integrity

• Run chkdsk C: /f /r on the affected volume to repair file system inconsistencies, especially within the $TxfLog and $Tops metadata streams.
• Schedule the check on next reboot if the volume is in use: type Y when prompted.
• After reboot, re-run fsutil resource info C: to confirm the resource manager initializes without error.

Step 3: Validate SQL Server Service Account Permissions

• Open Computer Management > Local Users and Groups, confirm the SQL Server service account has Full Control on the database directory (e.g., D:\SQLData\).
• Alternatively, use icacls "D:\SQLData" /grant "NT SERVICE\MSSQLSERVER":(OI)(CI)F to explicitly grant permissions.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Step 4: Rebuild the Resource Manager (If Corrupted)

• If the error persists, delete the orphaned TxF resource manager handle using fsutil resource delete C: (ensure no active transactions are pending first).
• Reboot the server to allow SQL Server to recreate the resource manager cleanly on next startup.
• Monitor the SQL Server Error Log (Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for confirmation that Error 5186 no longer appears.

Alternative or Verification Steps

• Verify the fix by querying SELECT * FROM sys.dm_tran_current_transaction; in SSMS to confirm transactional operations execute without triggering Event ID 5186 in the Windows Event Viewer (System log).
• If TxF remains unstable due to Microsoft’s ongoing deprecation of the feature, consider migrating filestream-dependent tables to FILESTREAM with FILETABLE disabled or moving to BLOB storage via Azure Blob integration, as TxF is not guaranteed in future Windows Server releases.