SQL Server dbcc clonedatabase – Gotcha

SQL Server dbcc clonedatabase​​ -​​ Gotcha



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.

  • Bob Dorr