Using DBCC CLONEDATABASE to generate a schema and statistics only copy of a user database in SQL Server 2014 SP2 | SansSQL

Sunday, October 30, 2016

Using DBCC CLONEDATABASE to generate a schema and statistics only copy of a user database in SQL Server 2014 SP2

DBCC CLONEDATABASE is a new DBCC command introduced in SQL Server 2014 SP 2 which is used for creating the clone of a specified user database which helps in troubleshooting the performance issues related to the query optimizer.

When a clone of the database is created using DBCC CLONEDATABASE, it will create a schema and statistics only copy of the specified database and does not contain any copy of the data.

Creating the clone is as simple as passing the source database name and clone database name to the DBCC command.
DBCC CLONEDATABASE ('SansSQL', 'SansSQL_Clone') 

Output of the DBCC Command

Once the cloning is completed, the cloned database will be in Read-Only mode.

SELECT name, database_id, is_read_only  
FROM sys.databases 
WHERE name in ('SansSQL', 'SansSQL_Clone') 

So what actually happens when we issue the DBCC CLONEDATABASE command on a database?
It will start with few validations before the clone is created, The following validations are performed by DBCC CLONEDATABASE. The command fails if any of the validations fail.
  • The source database must be a user database. Cloning of system databases (master, model, msdb, tempdb, distribution database etc.) isn't allowed.
  • The source database must be online or readable.
  • A database that uses the same name as the clone database must not already exist.
  • The command isn't in a user transaction.
If all the validations succeed, DBCC CLONEDATABASE will do the following operations:
  • Creating primary data file and log file
  • Adding secondary dataspaces
  • Adding secondary files
The destination database files will inherit the size and growth settings from the model database and the file names of the destination database will follow the source_file_name _underscore_random_number convention. 

SELECT database_id, file_id, type_desc, name, physical_name 
FROM sys.master_files 
WHERE DB_NAME(database_id) in ('SansSQL', 'SansSQL_Clone') 


Then the DBCC CLONEDATABASE will do a Internal Database Snapshot with the following steps
  • Validate the source database
  • Get S lock for the source database
  • Create snapshot of the source database
  • Create a clone database (this is an empty database which inherits from model)
  • Get X lock for the clone database
  • Copy the metadata to the clone database
  • Release all DB locks
Using the below command, we can check if a database is a clone or a normal database.
SELECT DATABASEPROPERTYEX('SansSQL','isClone') AS SansSQL_DB_CloneStatus 
      ,DATABASEPROPERTYEX('SansSQL_Clone','isClone') AS SansSQL_CloneDB_CloneStatus


Reference: https://support.microsoft.com/en-in/kb/3177838

1 comment:

Post a Comment