This blog post is about an error message I got the other day when using DBCC CLONEDATABASE in a T-sql-script. But first some background to DBCC CLONEDATABASE.
I was pretty excited about the DBCC CLONEDATABASE command, which was introduced in SQL Server 2014 SP2 and SQL Server 2016 SP1. It creates a schema-only (that means all the database objects, but no data) copy of a database, keeping all statistics data, so that you can troubleshoot Query plans for certain queries without having to copy all the data. Before DBCC CLONEDATABASE (and to be honest probably also afterwords, DBCC CLONEDATABASE doesn’t replace all the needs) one had to make a full copy of a database to get the statistics data along. That’s usually copied to a test box. If the test box is identical to your production box, you’re almost fine. But on your test box, you don’t have the cached execution plans from the production box. Therefore, you might end up with very different Query plans in your test box. With DBCC CLONEDATABASE, you get a readonly copy of a database, on your production box and you can use that to tweak your queries and see what new estimated execution plans they get.
But there are limitations to what you can do in terms of troubleshooting. You can’t really use it to test real queries, since the clone has no data. Not only will you miss the actual data when running queries, you won’t know anything about CPU or IO times for a Query execution. And you can’t add or change indexes to see how a Query will behave – you can of course make the clone a readwrite database and alter/add indexes. But any new or altered index will screw up the statistics objects – since the database contains no data, the statistics for the objects will be empty.
To me, DBCC CLONEDATABASE is not that useful for troubleshooting. My use for DBCC CLONEDATABASE is a replacement for scripting a database. I’m creating a database which should represent a subset of another database. When I first developed the scripts for it, I used Adventureworks2014, and sampled 250 rows from Sales.Customer, and wanted to get a complete Adventureworks2014 database for these 250 customers, including their orders, the orderlines for these orders, the salespersons for these customers, the rows from Person.Person, Sales.Store, Production.Products etc. I got a script to work OK on my own laptop, but when trying to run it against a server and Another database, I got an error message:
Msg 2601, Level 14, State 1, Line 4
Cannot insert duplicate key row in object ‘sys.sysclsobjs’ with unique index ‘clst’. The duplicate key value is (50, 5).
I created a Connect item for this error, but couldn’t really let it go, so I did some researching. sys.sysclsobjs is not available unless you open an Admin connection to your database. So I did that, and this is what I found:
I have a row in sys.sysclsobjs with class=50 and id=5. I found out that this row has information about a schema which exists in my source database. I also found that all rows with class=50 has information about my schemas (id=1 is dbo etc). The schema_id for each schema is represented by the id-column in sys.sysclsobjs. But this didn’t help me much – there was nothing strange with the schema. So I let it go for a while, had some coffee, helped some developers troubleshoot queries and went home from work.
But I still couldn’t let it go, so I fired up my home computer and tried to figure out what is different on my own laptop’s SQL Server instance compared to the server instance. Since the source database was the same on both my laptop and the server instance, the SQL Server build number is the same, I started looking at what could possibly be different between the instances.
Without going through all the attempts leading to a dead end (they were many…) I finally looked at the model database on my two instances. In the instance where I got the error, I had a schema created. A schema created by monitoring software. This schema had schema_id=5.
I removed all the user created schemas from model. And voila, DBCC CLONEDATABASE works.
What does this mean? To me, it looks like (this has not been verified, I draw my conclusions based on symtoms, not on any knowledge about the bits and pieces inside DBCC) DBCC CLONEDATABASE creates a new database, using a normal CREATE DATABASE command and then creates database objects and copies statistics. All schema_id’s and object_id’s are copied to the clone and this is what causes the command to throw the duplicate key error. When schema_id=5 is copied from Adventureworks2014 to Adventureworks2014_clone, there’s already a schema with schema_id=5 – the one copied from the Model system database.
I was pleased to find the cause of the error and on our test boxes, I’m ok with dropping schema_id=5 in Model and go on with life. But I won’t be equally happy when we start using my script for something more useful than subsetting Adventureworks2014. Subsetting a production database for troubleshooting purposes won’t be possible. Unless I let my script drop any non-system-schemas in Model in our production instances.
Here’s a script to reproduce the error:
--First create a user database
CREATE DATABASE CloneTest;
GO
USE CloneTest;
GO
CREATE SCHEMA CloneTestSchema;
GO
SELECT SCHEMA_ID('CloneTestSchema');
--schema_id=5
GO
--Now create a schema in Model
USE Model;
GO
SELECT * FROM sys.schemas ORDER BY schema_id;
GO
--Gives these first rows
-- name schema_id principal_id
-- dbo 1 1
-- guest 2 2
-- INFORMATION_SCHEMA 3 3
-- sys 4 4
-- db_owner 16384 16384
GO
CREATE SCHEMA model_schema;
GO
SELECT SCHEMA_ID('model_schema');
--schema_id=5
GO
USE master;
GO
--Now try to clone the CloneTest database
DBCC CLONEDATABASE('CloneTest','CloneTest_Clone');
--Database cloning for 'CloneTest' has started with target as 'CloneTest_Clone'.
--Msg 2601, Level 14, State 1, Line 32
--Cannot insert duplicate key row in object 'sys.sysclsobjs' with unique index 'clst'. The duplicate key value is (50, 5).
GO
--Now remove the schema with schema_id=5 from Model
USE model;
GO
DROP SCHEMA model_schema;
GO
USE master;
GO
--Clone again
DBCC CLONEDATABASE('CloneTest','CloneTest_Clone');
--Database cloning for 'CloneTest' has started with target as 'CloneTest_Clone'.
--Database cloning for 'CloneTest' has finished. Cloned database is 'CloneTest_Clone'.
--Database 'CloneTest_Clone' is a cloned database. A cloned 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.
GO
I thought for a while that it would be OK if the schema in model and the source database had the same schema_id/Schema_name-combination. But that also fails. So the only way to use DBCC CLONEDATABASE is if there are no user created schemas in model.
This helped me to fix. Thank you.