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

by

Troubleshooting Guide: Error 4992 – “Cannot use table option LARGE VALUE TYPES OUT OF ROW on a user table that does not have any of large value types varchar(max)…”

Why This Error Happens

This error occurs because the large value types out of row table option can only be applied to tables that actually contain at least one large object (LOB) data type column, such as varchar(max), nvarchar(max), varbinary(max), or xml. SQL Server 2025 validates the table schema before applying this storage setting, and if no qualifying column exists, the sp_tableoption call is rejected. This commonly happens when a script is reused across tables without verifying column data types, or when a column was previously altered from .max to a fixed-length type.

Step-by-Step Resolution Guide

Step 1: Verify the Table Schema

  • Open SSMS or run a query against INFORMATION_SCHEMA.COLUMNS to confirm column data types:
SELECT COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'YourTableName';
  • Confirm whether any column is varchar(max), nvarchar(max), varbinary(max), or xml.
  • If none exist, this confirms the root cause of Error 4992.

Step 2: Modify the Table to Include a Qualifying Data Type

  • If the table design requires LOB storage control, alter an existing column to a .max type:
ALTER TABLE YourTableName 
ALTER COLUMN YourColumn VARCHAR(MAX);
  • Ensure this change aligns with your data model and does not break downstream application logic (e.g., fixed-length assumptions in C# or Entity Framework models).

Step 3: Re-run the Table Option Command

  • Once a valid LOB column exists, re-execute the original statement:
EXEC sp_tableoption 'YourTableName', 'large value types out of row', 'ON';
  • Confirm no errors are returned in the Messages pane.

Step 4: Remove the Option If LOB Storage Isn’t Needed

  • If the table was never intended to store LOB data, simply skip applying this option.
  • Remove the line from your deployment script or migration file (e.g., DACPAC, SSDT project, or Flyway script) to prevent future failures during automated deployments.

Alternative or Verification Steps

  • Run sp_help 'YourTableName' after the fix to confirm the table option is now active and the schema includes the correct LOB column.
  • If the error persists in automated CI/CD pipelines (e.g., Azure DevOps or GitHub Actions on Windows Server 2025 runners), check the deployment script for hardcoded table names that may be pointing to the wrong object.