DBCC CLONEDATABASE returns error: Cannot insert duplicate key row in object 'sys.syssingleobjrefs

Question

I'm trying to run the following line of code:

DBCC CLONEDATABASE ([DBName],[DBName_Staging]) WITH VERIFY_CLONEDB

And I'm getting the following error:

Msg 2601, Level 14, State 1, Line 15 Cannot insert duplicate key row in object 'sys.syssingleobjrefs' with unique index 'clst'. The duplicate key value is (5, 51, 0).

I'm running on Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64)

From what I've read a possible solution is deleting the duplicate row from model. I know how to do it, I'm just not sure if it's safe, or if this is the best solution.

Any suggestions?

---

Based the query in this sqlskills article the "duplicate" objects in model are objects like queue_messages_99999999, syscommittab, filestream_tombstone_99999999, etc. I am trying to create copies of 30+ databases so moving/removing the objects in the user database side is complicated at best. Is it safe to remove these system objects?

• Charlieface: DBCC CHECKDB on the source DB?
• Kenneth Fisher: @Charlieface good thought but it comes up clean.
• beehive: Hello @KennethFisher, I have a very similar scenario and I was wondering what you did to overcome it. Thanks
• Kenneth Fisher: @beehive It's been a while so I'm not 100% certain but from what I can remember this was because model was from a much older version (upgraded in place) and had some legacy objects in it that I couldn't get rid of. As I recall in the end I gave up on using clonedatabase.

Answer 1

I've tested this on SQL Server 2019 Developer Edition and it seems like DBCC CLONEDATABASE doesn't support creation of clone if there are any user objects created in the model database.

DBCC CLONEDATABASE doesn't support creation of a clone if there are any user objects (tables, indexes, schemas, roles, and so on) that were created in the model database. If user objects are present in the model database, the database clone fails with following error message: Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object with unique index 'index name'. The duplicate key value is

You should probably drop the objects in model database before doing CLONEDATABASE command.

you can reproduce it by creating a test table on your source database and then create a similar table on model. Then on a new window execute the DBCC CLONEDATABASE command.

• sqL_handLe: Yeah - sometimes I see folks put sp_whoisactive or other stored procedures in the model database so it’ll be "inherited" by any new database created. But even adding stored procedures to model can lead to conflicts down the road in dbcc clonedatabase.
• Kenneth Fisher: Unfortunately in this case I can't find any non-system objects. There are some of the service broker objects but otherwise it's all system objects.