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

by

Troubleshooting Guide: SQL Server Error 3604 – “Duplicate Key Was Ignored”

Why This Error Happens

This error occurs when SQL Server attempts to rebuild or create a unique index and encounters rows with duplicate values in the column(s) being indexed. It commonly surfaces during DBCC operations, index maintenance tasks, or when restoring a database with a corrupted unique index definition. The root cause is typically a data integrity violation that existed before the unique constraint was enforced, or index metadata corruption following an improper shutdown.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Index and Table

  • Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to capture the full error context.
  • Note the Object ID and Index ID referenced alongside Error 3604 in the output.
  • Cross-reference the Object ID using SELECT OBJECT_NAME(object_id) FROM sys.indexes WHERE index_id = <ID>;

Step 2: Locate and Remove Duplicate Rows

  • Query the table using a GROUP BY on the indexed column(s) with HAVING COUNT(*) > 1 to surface duplicates.
  • Use a Common Table Expression (CTE) with ROW_NUMBER() to isolate and delete redundant rows while retaining the original record.
  • Example:
  WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY DuplicateColumn ORDER BY ID) AS rn
    FROM YourTable
  )
  DELETE FROM CTE WHERE rn > 1;

Step 3: Rebuild the Unique Index

  • Execute ALTER INDEX [IndexName] ON [YourTable] REBUILD; to restore the index structure.
  • If the index is severely corrupted, drop and recreate it manually using CREATE UNIQUE INDEX.
  • Ensure the rebuild path has sufficient disk space on the drive hosting C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.

Step 4: Validate Server and File Permissions

  • Confirm the SQL Server service account has Modify permissions on the data/log file directory via Windows Server 2025 File Explorer (Properties > Security tab).
  • Restart the SQL Server service using services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to clear any locked file handles.

Alternative or Verification Steps

  • Re-run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS; after the rebuild to confirm Error 3604 no longer appears in the output.
  • If duplicates persist due to application-level logic, consider implementing an IGNORE_DUP_KEY = ON option on the index as a temporary workaround while the application insert logic is fixed.