0

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.

9
  • I would definitely recommend not using Azure Files for your database files - you're going to likely have a ton of performance issues in addition to weird one-offs like this. – LowlyDBA - John M May 20 '20 at 17:29
  • LowlyDBA. Interesting why do you say that? Can you elaborate? – BBaggins May 20 '20 at 18:09
  • Azure Files has a lot of limitations that could be problematic. – LowlyDBA - John M May 20 '20 at 19:02
  • Thanks just read through that but not sure what in there is of particular interest. I'm new to this side of SQL. Come from a dev / dev-DBA background. Anything you can point out there as specifically problematic would be much appreciated. I've already come to the conclusion that Azure Files is not great for SQL, but I need to justify that and understand why. What would you suggest as a better Azure alternative for SQL storage? – BBaggins May 20 '20 at 19:15
  • Is there a reason you can't just provision storage on your VM normally? – LowlyDBA - John M May 20 '20 at 19:23
0

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.

9
  • Same problem for user databases with or without WITH TABLOCK. I get CHECKDB but it's not complete: 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 And on master I can't run CHECKDB at all - with or without WITH TABLOCK: The database could not be exclusively locked to perform the operation. I've had to offload CHECKDB to a server not using AF. And I can't check master on another server so it's not getting checked at all. Any way around this? – BBaggins May 21 '20 at 14:48
  • You can always flip the database to read-only, or restore a backup to a local disk, perhaps the scratch and checkdb that. – David Browne - Microsoft May 21 '20 at 14:53
  • Thanks but neither of those options works for master db. And even if either did work for master, I'm basically already doing the restore elsewhere and run CHECKDB option. Seems like if you can't run CHECKDB on master db and it's a pain on user databases, Azure Files is a no-go for SQL files. Right? I'd love to be wrong about this. – BBaggins May 21 '20 at 15:19
  • When you're storing the database on an SMB share, you're not really responsible for the storage solution. CHECKDB really detects storage failures. – David Browne - Microsoft May 21 '20 at 15:38
  • Interesting. So you're saying that it's redundant on my end as a DBA to be running that with Azure Files? Azure Files is robust enough where this will never be a problem? If not and there is corruption, would I get notified... somehow? – BBaggins May 21 '20 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.