Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Rocks!

SQL Database Administrator/Developer. Background in developing OLTP/document based databases, SQLXML and performance tuning with an unhealthy fascination for the SQL query optimiser!

There is currently a lease on the blob and no lease ID was specified in the request

I’ve been using the backup to Azure feature that shipped with SQL2012 SP1 CU2 and generally it works really well, but a couple of times I have had an issue where I wasn’t able to overwrite a previous backup file and received an error:

Msg 3202, Level 16, State 1, Line 1
Write on https://somestorage.blob.core.windows.net/somecontainer/MyDb.bak failed: Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (412) There is currently a lease on the blob and no lease ID was specified in the request..
Msg 3271, Level 16, State 1, Line 1
A nonrecoverable I/O error occurred on file https://somestorage.blob.core.windows.net/somecontainer/MyDb.bak: Error could not be gathered from Remote Endpoint.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

What seems to have happened is that a previous backup to that same filename was somehow terminated unexpectedly and has left the lease open to that file. The next time a backup was performed to that same file, it wasn’t able to overwrite it. I’m not too sure on why the original backup failed i.e. was it something to do my end with my local connection out to Azure or was it something in Azure that terminated my connection.. I really don't know at the moment, but either way, the original file wasn’t cleanly closed it seems.

The natural thing to attempt to do at this point is to see if you can manually delete the file, but when trying to do that from the control panel, you get a similar error message:

Capture

Looking at the file or rather blob in the storage container, it reports that the size is 1TB, even though I know that the backup would less then 1GB... Quite surprising really and different to what one is used to. Looks like that the blob size is only updated correctly when the lease is removed otherwise it reports this default file size of 1TB. Hope it doesn't interfere with my storage costs at the end of the month having these blobs reporting a size of 1TB for a while.

At this point I turned to google and tracked down a powershell script that at face value would solve my issue. But I couldn’t get it working as I think the scripts were developed for a different version of the WindowsAzure libraries that I had installed on my machine. So I fired up visual studio and converted the powershell script to a simple C# app that I can run as and when the issue occurs. This basically opens a connection to my storage account, checks for the lease against the file and if it is locked, it removes the lease and then deletes the file. I may try and convert this to a SQLCLR if I get this problem a lot so that I can handle this error as part of my backup routines.

using System;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Auth;
using Microsoft.WindowsAzure.Storage.Blob;
using Microsoft.WindowsAzure.Storage.Blob.Protocol;
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string accountName = "someaccount";
            string accountKey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
            CloudStorageAccount account;
            CloudStorageAccount.TryParse(
                string.Format("DefaultEndpointsProtocol=https;AccountName={0};AccountKey={1}"
                    , accountName, accountKey), out account);
            CloudBlobClient blobclient = new CloudBlobClient(account.BlobEndpoint, account.Credentials);
            CloudPageBlob blob = new CloudPageBlob(
                new Uri("https://someaccount.blob.core.windows.net/somecontainer/MyDb.bak")
                    , account.Credentials);
            blob.FetchAttributes();
            if (blob.Properties.LeaseStatus == LeaseStatus.Locked)
            {
                blob.BreakLease();
                blob.Delete(DeleteSnapshotsOption.None);
            }
        }
    }
}

Hope this helps someone as it frustrated me for a good few hours..

Enjoy!

Comments

Leave a comment on the original post [www.olcot.co.uk, opens in a new window]

Loading comments...