Error while restoring bacpac file to Tier-4 sandbox at step 12
Recently I noticed a recurring issue in Tier 4 sandbox when a previously exported bacpac is imported into the system, the import process stops execution at step 12 with following error :
Managed Sync Table Worker encountered an exception, but is continuing because ContinueOnError is true. Table Sync Failed for Table: SysIndexRebuildExclusionList.
Exception: System.InvalidOperationException: Database execution failed: Cannot create more than one clustered index on table 'DBO.SYSINDEXREBUILDEXCLUSIONLIST'. Drop the existing clustered index 'PERF_SYSINDEXREBUILDEXCLUSIONLIST_TABLEIDNUMBER_IDX' before creating another.
Could not create constraint or index. See previous errors.
ALTER TABLE DBO.SYSINDEXREBUILDEXCLUSIONLIST ADD CONSTRAINT I_20489TABLEIDX PRIMARY KEY CLUSTERED(PARTITION,TABLEIDNUMBER,SCHEMANAME); ---> System.Data.SqlClient.SqlException: Cannot create more than one clustered index on table 'DBO.SYSINDEXREBUILDEXCLUSIONLIST'. Drop the existing clustered index 'PERF_SYSINDEXREBUILDEXCLUSIONLIST_TABLEIDNUMBER_IDX' before creating another.
Could not create constraint or index
Solution:
I was able to fix the error by dropping the index PERF_SYSINDEXREBUILDEXCLUSIONLIST_TABLEIDNUMBER_IDX from the newly imported database while the opertiaon was in failed status. Once the Index is dropped, just click "Resume" in LCS.
remember to select the new database that is being imported, the old database will be postfixed with "_orig" during the process.
script : ALTER TABLE [dbo].[SYSINDEXREBUILDEXCLUSIONLIST] DROP CONSTRAINT [PERF_SYSINDEXREBUILDEXCLUSIONLIST_TABLEIDNUMBER_IDX]
GO
-Manish
*This post is locked for comments