Moved from: bobsql.com
The dbcc clonedatabase is a handy command to clone metadata and information such as query disk store. Then you can use the database for various activities, usually reporting - “Performance Tuning with Query Store in SQL Server and Azure”
SQL Server Books Online documentation for clonedatabase contains a special note that I want to focus this post on.
“The new database generated from DBCC CLONEDATABASE is primarily intended for troubleshooting and diagnostic purposes. In order for the cloned database to be supported for use as a production database, the VERIFY_CLONEDB option must be used.”
I have been testing SQL Server High Availability failovers in combination with database creates and drops, including clonedatabase. As the books online references highlight the clonedatabase is created in multiple stages:
-
Create new database
-
Copy metadata
-
Copy Schema
-
…
The clone is marked as complete and verifiable once all stages of the command complete. The gotcha occurs when the clonedatabase command is interrupted (Ex: failover, power outage, …) leaving a partially cloned database, perhaps even as part of an availability group and replicated.
The interruption leaves partial metadata or schema in the cloned database leading to SQL Server assertions and errors often generating dumps (SQLDump*’s.) It is common for a partially cloned database to generate lots of dumps as the background processes (ghost, QDS, …) periodically attempt to use the database, triggering the asserts and high severity error conditions. The persistent errors and associated dumps can be a factor leading to high availability failovers.
I have been working with the team which owns dbcc clonedatabase to address the partial clone scenarios. Until a solution is provided in a SQL Server product be sure to drop a partially cloned database from your system to avoid unwanted impacts.
