Picture this, you’re happily backing up your database to a Azure blob storage until suddenly it starts mysteriously failing with the error…
Write to backup block blob device https://****** failed. Device has reached its limit of allowed blocks.
What’s going on, nothing’s changed?!
Well, it has, the database has grown and the issue that you’re bumping into here is that there’s a limit to the number of blocks we can upload to blob storage. That limit is 50,000.
So what does that mean for our backups? Well, essentially this puts a limit on the size of a SQL backup file. But there are a few things that we can do to get around it.
Compress you backups
The first thing that you can do is to make that backup file smaller by compressing it.
BACKUP DATABASE SQLUndercover TO URL = 'https://SQLUndercover.blob.core.windows.net/backups/SQLUndercover.BAK' WITH COMPRESSION = 1
To be honest here, that’s something that I’d recommend you do with all your SQL backups. There’s really no real reason why you wouldn’t want to compress your backups, you’ll get faster backup and restore times due to the reduced disk IO and smaller backup files at a virtually negligible cost in CPU.
Change the MAXTRANSFERSIZE
Hold on a second, we’re talking about the size of backups and a limit on blocks but there’s an important bit of information that I haven’t given you here and that’s how big is a block?
The size of a block is defined by the backup’s MAXTRANSFERSIZE setting, the default for that is 1MB.
This means that a backup file can consist of up to 50,000 1MB blocks or a max file size of around 50GB.
MAXTRANSFERSIZE can be set to a maximum of 4MB (4194304 bytes). Doing that will now allow us to have 50,000 4MB blocks and bump up our max file size to 200GB.
BACKUP DATABASE SQLUndercover
TO URL = 'https://SQLUndercover.blob.core.windows.net/backups/SQLUndercover.BAK'
WITH COMPRESSION = 1,
MAXTRANSFERSIZE= 4194304
Backup to Multiple Files
But what if your backup file is still coming in over 200GB?
In that case, the last thing you can do is to run the backup off to multiple files. It really doesn’t matter how big the total size of the backup is, as long as none of the individual files exceed that max block count.
BACKUP DATABASE SQLUndercover
TO URL = 'https://SQLUndercover.blob.core.windows.net/backups/SQLUndercover1.BAK',
URL = 'https://SQLUndercover.blob.core.windows.net/backups/SQLUndercover2.BAK',
URL = 'https://SQLUndercover.blob.core.windows.net/backups/SQLUndercover3.BAK'
WITH COMPRESSION = 1,
MAXTRANSFERSIZE= 4194304
So, if you’ve found yourself stuck by this frustrating issue, I hope you’ve found this useful. Go through the steps in order, compression, MAXTRANSFERSIZE and then multiple files. It took me a while to twig that MAXTRANSFERSIZE determined the block size so I had databases backing up to a crazy number of files. As soon as I figured out that I could bump up that block size, life became so much easier.
Thanks for reading
SQL Undercover 