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

by

Troubleshooting Guide: DBCC Compaction Phase Progress Message (Error 2548)

Why This Error Happens

This message is not a true error but an informational progress notification generated during a DBCC DBREINDEX, DBCC INDEXDEFRAG, or online index rebuild operation, reporting the percentage completion of the compaction phase. It typically surfaces as a concern when the compaction phase stalls or takes excessively long, which usually points to heavy fragmentation, insufficient tempdb space, blocking from concurrent transactions, or disk I/O bottlenecks on the underlying storage. On Windows Server 2025 / Windows 11 hosts, this can be exacerbated by antivirus real-time scanning on SQL data files or insufficient memory allocated to the SQL Server instance, causing excessive paging during the sort/compaction work.

Step-by-Step Resolution Guide

Step 1: Identify Blocking and Resource Contention

  • Run sp_whoisactive or query sys.dm_exec_requests to check if the index operation is being blocked by another session.
  • Check sys.dm_os_waiting_tasks for wait types like PAGEIOLATCH, LCK_M_*, or RESOURCE_SEMAPHORE, which indicate I/O or memory pressure.
  • If blocking is found, identify the blocking SPID using DBCC INPUTBUFFER(<SPID>) and determine whether it is safe to terminate with KILL <SPID>.

Step 2: Verify and Expand tempdb Capacity

  • Navigate to Server Properties > Database Settings in SSMS or query sys.master_files to check current tempdb file sizes and autogrowth settings.
  • Ensure tempdb data files are pre-sized appropriately (e.g., several GB per file, with multiple files matching CPU core count up to 8) to avoid autogrowth stalls during compaction.
  • Example T-SQL to expand tempdb:
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 10240MB);

Step 3: Exclude SQL Server Files from Antivirus Scanning

  • On Windows Server 2025 / Windows 11, open Windows Security > Virus & threat protection > Manage settings > Add or remove exclusions.
  • Add exclusions for the SQL Server data, log, and tempdb directories, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
  • Also exclude sqlservr.exe from the C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ path from real-time process scanning.

Step 4: Rebuild the Index with Optimized Options

  • Instead of a full offline rebuild, use the ONLINE = ON option (Enterprise/Developer edition) to minimize locking during compaction:
ALTER INDEX ALL ON dbo.YourTable REBUILD WITH (ONLINE = ON, MAXDOP = 4, SORT_IN_TEMPDB = ON);
  • Adjust MAXDOP to match available CPU cores and set SORT_IN_TEMPDB = ON to offload sort operations and speed up compaction.

Step 5: Validate Disk I/O Performance

  • Use Windows Performance Monitor (perfmon.exe) to track the Avg. Disk sec/Transfer counter on the volume hosting the data files.
  • If latency exceeds 20ms consistently, consider moving data/log files to faster storage (NVMe/SSD) or separating data, log, and tempdb onto different physical disks.

Alternative or Verification Steps

  • After the rebuild completes, run DBCC CHECKDB WITH NO_INFOMSGS and verify sys.dm_db_index_physical_stats to confirm fragmentation has been reduced below 10%.
  • If the compaction phase continues to hang indefinitely, consider dropping and recreating the index instead of rebuilding, as this bypasses the incremental compaction logic entirely for large heavily fragmented tables.