I ran into this error recently while trying to clone a production database:

Msg 2601, Level 14, State 1, LineNumber

Cannot insert duplicate key row in object ‘sys.sysschobjs’ with unique index ‘clst’. The duplicate key value is key_value.

Attempts at querying this error were a mixed bag. There’s a Microsoft article here, which says that the issue is caused by “a key collision between the default service broker objects in the destination and the user objects in the source”. BUT: it only applied to databases with objects originally created in SQL Server 2000.

Well, maybe somebody’s error was caused by this, but it wasn’t mine. Also, this was apparently fixed in SP2 CU5 on SQL Server 2014 (which is what this database was running on), and I most definitely still had the problem.

The answer is actually in the error: it’s a duplicate key value being inserted into sys.sysschobjs. What it doesn’t tell you is which sys.sysschobjs.

A quick word about that table: you can’t query it unless you’re connected via the DAC (Dedicated Admin Connection). And even then, looking in just the database that’s having the problem won’t tell you anything: you’ve got to look in the same table in the model database as well.

I know precious little about the particulars of how DBCC CLONEDATABASE works, and I’m certainly not going to get into a deep dive to find out, given how infrequently I use it. But it’s clear that it is making use of the model database, and if there is a collision between object ID’s, the clone operation will fail.

I found the duplicate by modifying a query I found in a post from Erin Stellato (thanks, Erin!):

SELECT m.id, m.name, c.name, c.id, m.type
FROM model.sys.sysobjects m
FULL OUTER JOIN sys.sysobjects c
ON m.id = c.id
JOIN sys.objects o
ON c.id = o.object_id
WHERE --o.is_ms_shipped <> 1
m.name <> c.name
AND m.id IS NOT NULL;

I commented out the check on is_ms_shipped. The query found my culprit: a user-defined table type whose ID matched a system table in model. I dropped the dependent stored procedures, dropped and recreated the UDTT, and recreated the procedures. Voila! Cloning successful.

There are likely other little glitches that will prevent successful cloning, but you now have one more way to find them!