Can't Run DBCC CHECKDB on master DB - Azure Files

Question

Storing SQL Server database files on new Azure Files share. Cannot run full / comprehensive CHECKDB against these databases - I think this has something to do with user account not having permissions to create snapshots. As a result, I offloaded these checks to an alternate server where I can also test .baks. Everything works fine except for the master db, which registers corruption when you restore it as a user db and run CHECKDB against it (https://www.itprotoday.com/my-master-database-really-corrupt), even though it's not corrupt.

Questions:

1) Has anyone run into the same problem running CHECKDB on SQL db files stored on an Azure Files share? Is there a workaround?

2) What's an alternative to running CHECKDB on master if I cannot run it in PROD? Can I somehow restore master to another SQL instance and check it there?

Error when I execute DBCC CHECKDB (master) in PROD:

Msg 5030, Level 16, State 12, Line 4 The database could not be exclusively locked to perform the operation. Msg 7926, Level 16, State 1, Line 4 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.

Message when I run DBCC CHECKDB on user db in PROD:

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.

Answer 1

Please reference this Azure Support document: Error message when you run any of the DBCC CHECK commands in SQL Server: "The database could not be exclusively locked to perform the operation"

In Microsoft SQL Server, you may receive an error message when you run any of the following DBCC commands:

• DBCC CHECKDB
• DBCC CHECKTABLE
• DBCC CHECKALLOC
• DBCC CHECKCATALOG
• DBCC CHECKFILEGROUP

The error message contains the following text:

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.

Cause:

This problem occurs if the following conditions are true:

• At least one other connection is using the database against which you run the DBCC CHECK command.
• The database contains at least one file group that is marked as read-only.

Starting with SQL Server 2005, DBCC CHECK commands create and use an internal database snapshot for consistency purposes when the command performs any checks. If a read-only file group exists in the database, the internal database snapshot is not created. To continue to perform the checks, the DBCC CHECK command tries to acquire an EX database lock. If other users are connected to this database, this attempt to acquire an EX lock fails. Therefore, you receive an error message.

Resolution

To resolve this problem, follow these steps instead of running the DBCC CHECK command against the database:

1. Create a database snapshot of the database for which you want to perform the checks. For more information about how to create a database snapshot, see the "Create a Database Snapshot (Transact-SQL)" topic in SQL Server Books Online.
2. Run the DBCC CHECK command against the database snapshot.
3. Drop the database snapshot after the DBCC CHECK command is completed.

This document can give more helps to solve the problem.

Updates:

For the system databases it does not use database snapshots, but it will hold table locks.

You also an reference this blog: Checkdb giving error for master database: Mike Walsh gives us more things about the error.

Hope this helps.