Seek and Destroy: Purge Remnant Azure Blob Storage Files From Failed Backups
Ugh, what a mouthful.
Azure Blob Storage has given me fits as of late due to a supposed defect between SQL Server's BackupToUrl.exe functionality and Azure Blob Storage's propensity to endure "transient failures", allegedly due to performance and/or capacity rebalancing that takes place behind the scenes during untold conditions.
Full disclosure: Microsoft has announced a fix under KB4463320 available here as 2016 build 5206. I have not aggressively tested this fix yet as I'm always reticent to deploy new builds directly to production, regardless of their good intention. YMMV as always.
This isn't a post to air that laundry, though. Instead, I've had to write a workaround to handle these issues. I slapped this together to seek for backups of the affected size - 1024^4 - or 1TB.
I call this script manually, but one could also leverage an SSMS job as a means to hide the storage account key from prying eyes. Either way, this is a quick and easy way to hunt down remnant junk files caused by failed backup attempts to Azure Blob Storage. You could also strip out the foreach segment and simply select out the $blobdatalist.name to list all 1.000TB files for your own review first. Please don't run this script blindly as I suppose there is a 1:1,000,000,000,000 chance that your database is actually 1.000TB in size and you wrote a factual backup of this size to blob storage without compression. I don't have any backup files this size, so it wasn't a concern for me - i just have thousands of databases backing up to blob targets.
In my next post I will demonstrate how I keep my blob storage accounts clear of routine backup files.
Note: I referenced this in my code gist but want to be super clear - this script depends on an MS code sample in order to break blob leases. Often times these failed backups will also have a hung lease, preventing simple deletion. I've written this script as such to determine if the affected file is leased and, in the event that it is, breaking the lease prior to executing the delete. You can find that module HERE.
Azure Blob Storage has given me fits as of late due to a supposed defect between SQL Server's BackupToUrl.exe functionality and Azure Blob Storage's propensity to endure "transient failures", allegedly due to performance and/or capacity rebalancing that takes place behind the scenes during untold conditions.
Full disclosure: Microsoft has announced a fix under KB4463320 available here as 2016 build 5206. I have not aggressively tested this fix yet as I'm always reticent to deploy new builds directly to production, regardless of their good intention. YMMV as always.
This isn't a post to air that laundry, though. Instead, I've had to write a workaround to handle these issues. I slapped this together to seek for backups of the affected size - 1024^4 - or 1TB.
I call this script manually, but one could also leverage an SSMS job as a means to hide the storage account key from prying eyes. Either way, this is a quick and easy way to hunt down remnant junk files caused by failed backup attempts to Azure Blob Storage. You could also strip out the foreach segment and simply select out the $blobdatalist.name to list all 1.000TB files for your own review first. Please don't run this script blindly as I suppose there is a 1:1,000,000,000,000 chance that your database is actually 1.000TB in size and you wrote a factual backup of this size to blob storage without compression. I don't have any backup files this size, so it wasn't a concern for me - i just have thousands of databases backing up to blob targets.
In my next post I will demonstrate how I keep my blob storage accounts clear of routine backup files.
Note: I referenced this in my code gist but want to be super clear - this script depends on an MS code sample in order to break blob leases. Often times these failed backups will also have a hung lease, preventing simple deletion. I've written this script as such to determine if the affected file is leased and, in the event that it is, breaking the lease prior to executing the delete. You can find that module HERE.
Comments
Post a Comment