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

by

Troubleshooting Guide: Error 2585 — “Cannot find partition number %ld for table”

Why This Error Happens

This error occurs when SQL Server’s internal metadata referencing a table’s partition structure becomes out of sync with the actual partition data stored in the underlying system catalogs (sys.partitions, sys.system_internals_partitions). It is typically triggered by corruption in the system catalog after an unexpected shutdown, a failed partition split/merge/switch operation, or disk-level corruption affecting metadata pages. In some cases, this also surfaces when a database is restored or attached from a source where partition function boundaries were altered without a corresponding rebuild of dependent indexes.

Step-by-Step Resolution Guide

Step 1: Run a Full Consistency Check on the Database

  • Open SQL Server Management Studio (SSMS) or use sqlcmd connected to the affected instance.
  • Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
  • Review the output specifically for errors referencing partition IDs, sys.partitions, or allocation unit mismatches.
  • Note the exact table name and object ID reported alongside Error 2585 for targeted repair.

Step 2: Validate Partition Function and Scheme Alignment

  • Query partition metadata directly to compare expected vs. actual partitions:
  • SELECT * FROM sys.partitions WHERE object_id = OBJECT_ID('YourTableName');
  • SELECT * FROM sys.partition_range_values WHERE function_id = (SELECT function_id FROM sys.partition_functions WHERE name = 'YourPartitionFunctionName');
  • Confirm the partition count returned matches the number of boundary values defined in the partition function.
  • If a mismatch exists, this confirms metadata desynchronization rather than physical disk corruption.

Step 3: Rebuild Indexes to Force Metadata Resynchronization

  • Rebuild all indexes on the affected table to force SQL Server to regenerate partition mapping:
  • ALTER INDEX ALL ON YourTableName REBUILD PARTITION = ALL;
  • If the table is a heap, create a clustered index temporarily and drop it afterward to force a full rewrite.
  • Monitor the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) during the rebuild for any recurring 2585 entries.

Step 4: Restore From a Known Good Backup (If Corruption Persists)

  • If DBCC CHECKDB reports allocation errors that cannot be repaired via rebuild, perform a restore using a clean backup taken prior to the corruption event.
  • Use RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabase.bak' WITH REPLACE, RECOVERY;
  • Re-apply any transaction log backups taken after the base backup to minimize data loss.

Step 5: Repair as a Last Resort (Data Loss Risk)

  • If no valid backup exists, run DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) only as a final option.
  • Take the database into single-user mode first: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Revert to multi-user mode after repair completes: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Alternative or Verification Steps

  • Re-run DBCC CHECKDB and query sys.partitions again to confirm the partition count now matches the partition function boundaries and no errors are reported.
  • If the issue reoccurs on the same table repeatedly, check Windows Event Viewer (Application and System logs) under Event Viewer > Windows Logs for underlying disk I/O or storage subsystem errors that may be causing recurring metadata corruption.