DBCC CHECKDB Fails on a Database with files in Azure Blob Storage

  • Article

Starting with SQL Server 2014 you can use SQL Server with Data Files in Microsoft Azure Blob Storage. This feature is very common when you work with SQL Server on Azure VM's, the steps to accomplish this are:

  • Create an Azure VM and then Install SQL Server 2014 or above.
  • Create a Storage Account
  • Create a Container with Private access
  • Create a SAS Key
  • Create a database in SQL Server with database files in the Blob container

Check this Tutorial for more reference.

The result is a Database storing their files in a Blob Container

dbcc_err_img_01

As database administrator you are worried about database integrity, and for that reason, you scheduled a job or maintenance plan to check for the database integrity.

The first execution of the DBCC CHECKDB command runs without a problem.

CHECKDB found 0 allocation errors and 0 consistency errors in database 'ADWorks2016'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Problem

After a week in the second execution of the DBCC CHECKDB you get the following error:

Msg 1823, Level 16, State 8, Line 32
A database snapshot cannot be created because it failed to start.
Msg 5120, Level 16, State 155, Line 32
Unable to open the physical file "https://mssaalwaysonr1.blob.core.windows.net/mssql01adw/ADWData.mdf_MSSQL_DBCC9". Operating system error 80: "80(The file exists.)".
Msg 7928, Level 16, State 1, Line 32
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.

Workaround

The workaround started by looking into the container:

dbcc_err_img_04

"DBCC CHECKDB uses an internal database snapshot for the transactional consistency needed to perform these checks." This Snapshot is deleted after the execution of the DBCC but in this case the Snapshot file was not deleted.

The following step, was to look the permissions for the SAS in the container Policy. (Azure Storage Explorer)

dbcc_err_img_05

Conclusion

The credential defined in SQL Server to access to the container uses a SAS (Shared Access Signature) and it has a Policy without the Delete permission.

Resolution

Grant the Delete Permission to the Access Policy associated with the SAS in the container and manually Delete the old Snapshot file.

dbcc_err_img_06 dbcc_err_img_07

Now after the execution of the DBCC CHECKDB the Snapshot file is deleted automatically and there is no problem with the next execution.