Anybody out there using Azure Files (https://docs.microsoft.com/en-us/azure/storage/files/storage-files-introduction) for SQL data and log file storage? I’ve got one SQL Server using an AF share in this manner and am having issues running CHECKDB on its databases. Has anyone out there used Azure Files for SQL stuff and, if so, have you run into the following problem?
When attempting to run CHECKDB on master:
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked.
See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
When attempting to run CHECKDB on user database:
DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.
DBCC CHECKDB uses a Database Snapshot by default. Database Snapshots are an NTFS feature. When storing the database on an SMB share, they aren’t available. Try specifying the TABLOCK option for DBCC CHECKDB.
And Azure Premium Files are intended to support SQL Server database file storage. In addition to providing shared storage for clusters, they allow you to share storage resources among SQL VMs. Before the release of Premium Files, Azure Files was not really recommended for SQL Server database storage.
When Azure Shared Disks are GA they may provide a better option for shared storage, but currently the only two options are Storage Spaces Direct, and Azure Premium Files.