SQL Clone 5

The files behind the Clone have grown to be very large 

The differencing disk does not inflate except when there is a database write. Users have sometimes seen this grow very quickly when they had various automatic maintenance operations set up.

The amount of inflation depends on how much data in the clone is 'touched' by the filesystem. If a byte is changed, and then changed back to its original value, the clone will not deflate in response.

Reasons why a clone may inflate include:

  • Adding new data to the database
  • Updating existing data in the database
  • Deleting data from the database
  • SQL Server Ghost cleanup process - SQL Server background process that deletes records off of pages that have been marked for deletion.
  • Being upgraded by the instance - e.g. a SQL Server 2012 image being cloned into a SQL Server 2019 instance
  • Rebuilding database indexes
  • Adding files to FILESTREAM databases
  • Having Query Store enabled in the database
  • Enabling encryption like TDE
  • Any other operation that makes changes to the database files

Running substantial image modifications, and then cloning the modified image, may also cause clones to inflate. In this case, disabling database indexes just before the modification and then rebuilding them afterward may resolve this. For example:

MyImageModification.sql

ALTER INDEX MyIndex ON MyTable DISABLE;

UPDATE MyTable SET ... ;

ALTER INDEX MyIndex ON MyTable REBUILD;

After a database has been significantly modified, SQL Server slowly cleans up its file pages using the ghost cleanup process. This takes some time, and may result in slowly-inflating clones if an image is taken of a recently-modified database - as the ghost cleanup process then modifies the clones instead. If your clones are inflating and this situation seems right, you may wish to suspend ghost cleanup, or force cleanup manually as an image modification script.

SQL Server also has automatic processes that flush their work to disk periodically - for example, if you rebuild indexes or disable query store as an image modification. If you're still seeing clone inflation after trying some of these steps, you may want to try simply waiting a few minutes during your image modification for these processes to finish their work - as otherwise they will be picked up again on all of the image's clones. You can use WAITFOR DELAY as an image modification to simply wait around for SQL Server to complete its work before closing up the image.

WAITFOR DELAY '00:05'; /* wait for five minutes */

Didn't find what you were looking for?