DBCC DATABASECLONE fails on sys.sysowners - by Ben Whitman

Status : 

 


23
0
Sign in
to vote
ID 2926086 Comments
Status Active Workarounds
Type Bug Repros 6
Opened 7/13/2016 1:26:39 AM
Access Restriction Public

Description

In build 12.0.5000 (SQL 2014 SP2) I attempted to use the new DBCC CLONEDATABASE command on an existing database. I got the following error:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'sys.sysowners' with unique index 'nc1'. The duplicate key value is (<username>).

where <username> is one of the users in the database I chose for cloning.

Other databases on the same instance clone fine.

So it looks like for some reason it is trying to insert the row for this user twice into the clone.

Incidentally I ran a DBCC CHECKDB on the db it's failing to clone and that reported no errors.
Sign in to post a comment.
Posted by Erin Stellato on 6/14/2017 at 11:40 AM
Correct to my earlier comment - the issue is because ids in model changed between SQL 2000 and SQL Server 2005. The IDs 1977058079, 2009058193, and 2041058307 are the ones first used for user tables in SQL Server 2000. In SQL Server 2005 in model those are used for QueryNotificationErrorsQueue, EventNotificationErrorsQueue and ServiceBrokerQueue, respectively.

Posted by Erin Stellato on 6/14/2017 at 9:16 AM
You can use the following query to determine if you have an issue. It looks like model was rebuilt in 2016 to have different ids for objects. Therefore, if a user database was created before 2016 you can have objects that have the same id as system objects in model. You can also have system objects in the user database that do NOT have the same id as the same system object in model - not sure if this will also be a problem. Is there a workaround to address THIS specific problem? Recreating my user objects isn't really feasible - some of these objects are really large.

/*
    lists tables in the user database that have an ID that matches
    an object in model
*/
select m.id, m.name, c.name, c.id, m.type
from model.sys.sysobjects m
FULL OUTER JOIN user_database.sys.sysobjects c
    ON m.id = c.id
JOIN user_database.sys.objects o
    ON c.id = o.object_id
where o.is_ms_shipped <> 1
and m.id is not null

/*
    lists system objects in the user database that do *not* match
    the id of the same object in model
    can occur when a database was created in an earlier version of SQL Server
*/
select m.id, m.name, c.name, c.id, m.type
from model.sys.sysobjects m
FULL OUTER JOIN user_database.sys.sysobjects c
    ON m.name = c.name
JOIN user_database.sys.objects o
    ON c.id = o.object_id
where m.id != c.id

Posted by js_0505ps on 5/3/2017 at 12:16 PM
I can reproduce this error and I am getting another one. Cannot insert duplicate key row in object 'sys.sysclsobjs' with unique index 'clst'. The duplicate key value is (50, 8).
Posted by Pam Lahoud on 11/18/2016 at 1:17 PM
Update: Turns out I had a user-defined role in the model database, dropped that and the clone worked.
Posted by Pam Lahoud on 11/18/2016 at 12:11 PM
I'm seeing a similar error on SQL 2016 SP1:
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'sys.syssingleobjrefs' with unique index 'clst'. The duplicate key value is (5, 51, 0).

I don't see any user created objects in model, anything else that could cause this?
Posted by JWOL on 9/1/2016 at 2:31 AM
UPDATE: The object ID which is referenced as the duplicate key value refers to the service queue QueryNotificationErrorsQueue in the model database and a default constraint in the user database. Is there a workaround in this case?
Posted by JWOL on 9/1/2016 at 2:17 AM
I get the following error when trying to clone a user database:

Cannot insert duplicate key row in object 'sys.sysschobjs' with unique index 'clst'. The duplicate key value is (1977058079).

I have checked the model database and there are no user objects present.
Posted by Microsoft on 7/26/2016 at 12:17 PM
Thanks Ben and others for reporting and voting for the issue. This error is observed when user objects (non-default objects) are created in model database (for instance adding user tables, non-default users or login to model database) which are also available in user database which got duplicated from model database during database creation. The current workaround is to avoid or temporarily remove user objects from model database (users,tables), create clone and re-add them if required. We will be investigating and prioritizing it to provide a fix for the issue until then we recommend to leverage the workaround to be able to successful create database clone.
Posted by Microsoft on 7/25/2016 at 10:44 AM
Hi Ben,

Thank you for reporting the issue. Do you have any user objects created in your model database which also got copied in your database producing error when the database was created. Can you confirm if there are no user objects created in model database except the default system tables. Also would it be possible for you to share the database if required for diagnosing the issue?

Thanks !!!

Parikshit
Posted by Danilo C Braga on 7/13/2016 at 11:03 AM
I also got a similar error:
Cannot insert duplicate key row in object 'sys.sysschobjs' with unique index 'clst'. The duplicate key value is ().