Like Compounding Interest – For Backups!

I got our backup schedule back under control. Here’s what happened. And maybe there’s some useful info here for others. We do nightly FULL backups, with Hourly Differentials during business hours.

The Gist: If you run out of space for a Full Backup, your Differentials are going to eat you alive.

Here’s the way it works. A full Backup is performed (in my case, it’s around 35GB). When the Full Backup is performed, the Differentials are now just diffs from that point in time, and they start out small, say 20-30MB each hour. By the end of the day, the Differentials are getting to be a significant size (5 to 6 GB), begging for the nightly Full.

But, if you run out of disk space during the nightly Full Backup, the Differentials do not reset. They keep growing, waiting for that Full Backup to complete. Your failed 35GB backup is now causing your hourly Diffs to continue growing. Now, you are taking HOURLY Diffs that are 6GB… 7GB… 8GB, – PER HOUR, and so on – further compounding the problem. My Differentials were approaching the size of the Full Backup! What the…? Are we THAT busy?

I would clear out 60GB of space thinking, “That’s surely enough to complete a 40GB Full Backup”. But by the time the nightly backup came around on the guitar, the Diffs had eaten half that space, and …. Full Backup Fails. Round we go.

What you need to do is clear enough space on the backup device to contain not just the expected nightly Full Backups, but also all the Differentials that are going to accrue before then!


About combatdba

I'm a production DBA at a terabyte-class SQL Server Shop
This entry was posted in S.W.A.T.. Bookmark the permalink.

One Response to Like Compounding Interest – For Backups!

  1. Brent Ozar says:

    That is a REALLY good way of putting it! I never thought of it as compound interest, and that’s exactly what it is.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s