Shrinking images using SQL Scripts

jakobehn

I'm trying to reduce the size of the generated images by running TRUNCATE and DBCC SHRINKDATABASE as part of the modification SQL scripts, but to no effect. The image actually ends up slightly bigger than before, when I tested this on some databases.

I can't really find any documentation or guidance around this, but I figure it must be a quite common case. I know that there are some limitation in what can be done by the SQL modification scripts, since they are executed through a user that doesn't have full access. 

Thanks!
/Jakob

owen.hall

Hi jakob,

Right now, there's no facility for images to shrink when the amount of data inside them is reduced. However, making this possible is a fairly common request, so we intend to tackle this issue fairly soon - probably within the next couple of months. Keep an eye on the release notes!

jakobehn

Thanks Owen, 

I'll keep an eye then, this would really help us reduce the amount of storage being used for the images

sweston

I second this request.

jakobehn

Any news on when this feature will be available?

Thanks
/Jakob

owen.hall

Hi everyone,

This was just released for PowerShell in SQL Clone 4.4. We'll be working on adding it to the web UI soon.

There's now a -CompactImage parameter to New-SqlCloneImage. If left unset, it'll default to None (current behaviour - no compaction). Setting it to Compact will shrink the image file after the modifications step, so if you've used modifications to reduce the amount of data in your database and then shrink it (i.e. dropping tables and then running DBCC SHRINKDATABASE), you should find yourself with a smaller image.

Image compaction isn't supported on Windows 7 or Server 2008 R2, as the underlying compaction feature is wildly different in performance and reliability on those versions of Windows. Image creation will proceed, but the compaction parameter will be ignored.

Thanks!