DevOps: Avoiding SQL Server Clone databases growing way too large

I’ve recently been making use of SQL Clone from Redgate, at various client sites. I have to say that I really like it.

The basic concept is that you take an existing SQL Server database, you create an “image” from it, then you create database clones from that image.

Under the covers, it’s basically a differencing technology. The initial image is like an initial set of database files, and each clone is then files that are differenced from that. That makes it very easy to spin up clones, and to break them down again. While this isn’t really suitable for much in the way of performance or load testing, as everyone is sharing the same base, it’s perfect for general development.

It’s also a really good fit for use with source control systems like Git that developers endlessly create branches in. You can just create a clone for use with each branch, and avoid developers trampling on top of each other all the time. Each developer gets to have separate databases (or even multiple databases) and gets to test on full data volumes, without all the storage space that would otherwise be required.

What I have run into lately though, is a site where all of the clone databases were getting bigger and bigger constantly. Instead of being 40 or 50 MB as they started out, each was now taking hundreds of gigabytes.

That was pretty easy to track down, and it was caused by routine database maintenance tasks that the customer was scheduling. In particular, they were regularly rebuilding the indexes in the database. In a clone database that’s based on differencing technology, that’s not going to make sense at all. Rebuilding 200GB of tables or indexes could easily end up with a 200GB clone fine. And if you do that to all your clones, well you can see the problem.

What is needed is to avoid performing index maintenance on the clone databases. Given that to SQL Server, they just look like any other database, I’d suggest having a naming scheme for the clones, to make it obvious which they are, to exclude them from these operations.

2018-03-09