Sample Header Ad - 728x90

Azure Block Blob Limitation

2 votes
1 answer
5805 views
Recently I ran into an error when I tried to backup a database to Azure Blob storage. The error was this: > 1117(The request could not be performed because of an I/O device error.) The database .mdf file was about 80gb. Upon reading a few postings, it turns out Azure Blob storage does have some type of limitation. If I understand correctly, there are only 50,000 blocks available and each block can hold roughly 4Mb. I played around with the following BACKUP DATABASE TO URL with varying parameters: 1. BACKUP DATABASE [mydb] TO URL WITH INIT (failed) 2. BACKUP DATABASE [mydb] TO URL WITH INIT, COMPRESSION (succeeded) 3. BACKUP DATABASE [mydb] TO URL WITH INIT, COMPRESSION, MAXTRANSFERSIZE = 4194304 (failed) 4. BACKUP DATABASE [mydb] TO URL WITH INIT, COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536 (succeeded) When the operation was successful, the backup file produced was about 7.8Gb. Again, the .mdf file was about 80Gb. I'm a bit confused as to how the parameter BLOCKSIZE is used as part of the BACKUP T-SQL command. Can someone explain why the operation would fail when this parameter is omitted?
Asked by sydney (167 rep)
Aug 23, 2021, 01:54 PM
Last activity: Aug 25, 2021, 05:28 PM