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

by

Troubleshooting Guide: SQL Server Error 4901

Why This Error Happens

This error occurs because SQL Server’s ALTER TABLE statement enforces a safety constraint: when adding a new column to a table that already contains rows, the new column must either allow NULL values or have a DEFAULT constraint defined. Without one of these, SQL Server has no value to populate for the pre-existing rows, so it rejects the operation to prevent data integrity violations. This commonly surfaces when migration scripts or ORM-generated schema changes (e.g., Entity Framework, Liquibase) define a column as NOT NULL without supplying a default value.

Step-by-Step Resolution Guide

Step 1: Identify the Offending ALTER TABLE Statement

  • Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio and locate the exact script that triggered Error 4901.
  • Check the column definition for a missing NULL or DEFAULT clause, for example:
  ALTER TABLE dbo.Customers ADD LoyaltyTier VARCHAR(20) NOT NULL;

Step 2: Modify the Script to Allow Nulls or Provide a Default

  • Add a DEFAULT constraint so existing rows are auto-populated:
  ALTER TABLE dbo.Customers ADD LoyaltyTier VARCHAR(20) NOT NULL DEFAULT 'Standard';
  • Alternatively, allow nulls temporarily, backfill data, then enforce NOT NULL:
  ALTER TABLE dbo.Customers ADD LoyaltyTier VARCHAR(20) NULL;
  UPDATE dbo.Customers SET LoyaltyTier = 'Standard' WHERE LoyaltyTier IS NULL;
  ALTER TABLE dbo.Customers ALTER COLUMN LoyaltyTier VARCHAR(20) NOT NULL;

Step 3: Check ORM or Migration Tool Configuration

  • If using Entity Framework Core, review the migration file (Migrations/*.cs) and add .HasDefaultValue() in the OnModelCreating or migration Up() method.
  • For Liquibase/Flyway scripts, confirm the defaultValue attribute is set in the changeset XML/YAML.

Step 4: Re-run the Schema Update

  • Execute the corrected script against the target database on Windows Server 2025.
  • Confirm the SQL Server Agent job or deployment pipeline (e.g., Azure DevOps, GitHub Actions) completes without throwing Error 4901 again.

Alternative or Verification Steps

  • Run SELECT COLUMN_NAME, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Customers'; to confirm the new column has the expected nullability and default value.
  • If the table is empty (zero rows), you can safely add a NOT NULL column without a default — verify row count first with SELECT COUNT(*) FROM dbo.Customers;.