This is one of the features which I wanted to demonstrate to my clients while delivering my service Comprehensive Database Performance Health Check. So, while preparing for a demo I encountered a strange error. In this blog we would learn about fixing error: Cannot insert a duplicate key row in object ‘sys.sysschobjs’ with unique index ‘clst’ while using DBCC CLONEDATABASE.
The command which I executed was:
And the error is below:
Database cloning for ‘AdventureWorks2016CTP3’ has started with target as ‘AdventureWorks2016CTP3_08’.
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘sys.sysschobjs’ with unique index ‘clst’. The duplicate key value is (885578193).
I wanted to go further to fix it but ‘sys.sysschobjs’ is a system object and it was giving error while querying
Msg 208, Level 16, State 1, Line 6
Invalid object name ‘sys.sysschobjs’.
I realized that I need to connect via DAC connection. I logged in via DAC (ADMIN:SERVERNAME in SSMS) and ran below query to find what we have in AdventureWorks2016CTP3 and model database.
USE AdventureWorks2016CTP3 GO SELECT * FROM sys.sysschobjs WHERE id = 885578193 GO USE model GO SELECT * FROM sys.sysschobjs WHERE id = 885578193 GO
This gave me below output:
As we can see now, I have a table in the Model database which is having the same ID.
WORKAROUND/SOLUTION – CLONEDATABASE
Since the ID was matching, I dropped the table in the model to get rid of that same ID. I tried to drop and recreate but it gave me the error again with new id. Finally, I dropped the table from the model database and cloning succeed.
Database cloning for ‘AdventureWorks2016CTP3’ has started with target as ‘AdventureWorks2016CTP3_clone’.
Database cloning for ‘AdventureWorks2016CTP3’ has finished. Cloned database is ‘AdventureWorks2016CTP3_clone’.
Database ‘AdventureWorks2016CTP3_clone’ is a cloned database. This database should be used for diagnostic purposes only and is not supported for use in a production environment.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
In short, if you encounter any error in cloning the database check model database first to see if there is any conflicting object.
Reference: Pinal Dave (https://blog.sqlauthority.com)