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

by

Troubleshooting Guide: Error 1984 — Index Row Length Exceeds Maximum Allowed Size

Why This Error Happens

This error occurs when the total row size of an index, calculated using the vardecimal (or later, the related sparse/compressed row) storage format, exceeds SQL Server’s maximum row size limit of 8,060 bytes once overflow estimation is applied. It typically happens after adding new columns, changing decimal/numeric precision, or enabling vardecimal storage on a table with already wide rows. SQL Server cannot build or rebuild the index because the engine cannot guarantee that all variable-length decimal data will fit within a single page row, even though actual stored data may currently be smaller.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Table and Index

  • Run the failing CREATE INDEX or ALTER INDEX ... REBUILD statement again and note the exact index name and table from the error message.
  • Query estimated row size using:
  EXEC sp_spaceused 'YourTableName';
  SELECT SUM(max_length) AS EstimatedRowSize
  FROM sys.columns
  WHERE object_id = OBJECT_ID('YourTableName');
  • Confirm whether vardecimal storage format is enabled:
  EXEC sp_db_vardecimal_storage_format 'YourDatabaseName';

Step 2: Reduce the Effective Row Size

  • Review decimal/numeric columns included in the index key or included columns; reduce precision/scale where possible (e.g., decimal(38,10) → decimal(18,4)).
  • Move large or rarely queried columns out of the index definition and into non-indexed columns.
  • Consider splitting extremely wide tables vertically (move less-used columns into a separate related table).

Step 3: Enable Row/Page Compression Instead of Vardecimal (Recommended for SQL Server 2025)

  • Vardecimal is a legacy storage format; in modern SQL Server versions, switch to native row or page compression instead:
  ALTER TABLE YourTableName REBUILD WITH (DATA_COMPRESSION = PAGE);
  • This compresses decimal and other fixed-length types more efficiently without the same row-size constraints as vardecimal.
  • Re-run the failed index creation/rebuild statement after compression is applied.

Step 4: Rebuild the Index with Adjusted Schema

  • Once the row size is reduced below the 8,060-byte threshold, rebuild the index:
  ALTER INDEX ALL ON YourTableName REBUILD;
  • Monitor for Error 1984 recurrence during the rebuild operation.

Step 5: Update Statistics and Validate Database Integrity

  • Run DBCC CHECKDB('YourDatabaseName') to confirm no underlying corruption contributed to the row-size miscalculation.
  • Update statistics post-rebuild:
  UPDATE STATISTICS YourTableName;

Alternative or Verification Steps

  • Verify the fix by re-executing the original CREATE INDEX/ALTER INDEX statement — it should complete without Error 1984, and sys.indexes should show the index as is_disabled = 0.
  • If the issue persists despite compression and precision reduction, consider redesigning the table using sparse columns or splitting it into multiple narrower tables to permanently stay under the 8,060-byte row limit.