DBCC CHECKDB database snapshot failure

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.

If you encounter this error, this blog post may help you in your troubleshooting.

When integrity check is performed with DBCC CHECKDB or a more granular integrity check operation like DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG, it chooses one of these two methods to perform the operation.

1) Obtain locks with TABLOCK option
2) Internal database snapshot (Introduced in SQL Server 2005)

By default, DBCC CHECKDB and other granular integrity operations use an internal database snapshot. It prevents blocking and concurrency problems during the operations. However, if a snapshot could not be created or TABLOCK option is used, the operation acquires locks to obtain the required consistency. More information at this Microsoft article.

The error encountered stated that the database snapshot could be created and locks could not be obtained.

Let's dive into some tests.

In this example, we have previously created a folder in separate disk drive (In this case, D:\MSSQL11.MSSQLSERVER\MSSQL\Data)

CREATE DATABASE Test
ON PRIMARY 
(
 NAME = Test,
 FILENAME = 'D:\MSSQL11.MSSQLSERVER\MSSQL\Data\Test.mdf'
)
LOG ON
(
 NAME = Test_log,
 FILENAME = 'D:\MSSQL11.MSSQLSERVER\MSSQL\Data\Test_log.ldf'
);

First we are going to test the lock option. Open a transaction and perform a integrity check with lock option.

USE TEST;

BEGIN TRAN;

CREATE TABLE dbo.tbl1
(
 col1 int DEFAULT (1)
);

--ROLLBACK;

Verify there is an open transaction in Test Database,

DBCC OPENTRAN (Test);

Now in another query window, run the integrity check,

DBCC CHECKDB (Test) WITH TABLOCK;

It throws an error, the one listed above. This is expected as the DBCC CHECKDB with lock option couldn't obtain the exclusive lock since there is an open transaction. If it is to use the default method by utilizing internal database snapshot, it does not required to obtain the lock. By leaving the previous transaction open, execute this command,

DBCC CHECKDB (Test);

The reason for creating an open transaction is that the integrity check operation will have to resort to internal database snapshot method. If SQL Server is able to create the internal database snapshot during the integrity check operation, then it completes successfully. However, what about if SQL Server could not create that internal database snapshot, which likely to be the reason you are searching the internet for reasons and solutions.

Besides the error listed above, there is no other useful or detailed error in Windows log or SQL Server log that shows what is preventing the database snapshot creation. After a few tests, it ends up that SQL Server service account (NT SERVICE\MSSQLSERVER in this case) needs to be granted with at least READ permission to the root volume where the database files are located. Remember that the database is created in D:\ drive? The service account needs to be granted with at least READ permission to root volume (D:). The permission doesn't have to explicitly granted to the service account. Any of the user/groups listed will do,

1) SQL Server Service Account
2) Everyone
3) NT Authority\Authenticated Users
4) Users (e.g. TestServer\Users). This group usually includes NT Authority\Authenticated Users

The idea is that the SQL Server service account needs at least READ access to the root volume.

For the sake of testing, I remove all the user/group above from root volume, in my case D: drive (don't do this in production). Making sure there is open transaction, and run DBCC CHECKDB with the default option. It failed with above error message.

By granting either one of the user/group above with READ permission to the root volume. The DBCC CHECKDB with default option complete successfully.

This permission issue also causes some dynamic management function not returning results. More information in this post.

This is tested on SQL Server 2008 R2 and SQL Server 2012 on Windows 2008 R2 and Windows 2012.

Note: Paul Randal mentioned in one of his old post stating that SQL Service account must have privilege to create files in the DATA directory of the SQL Server database. It does not work in my case. Regardless of SQL Service account has this privilege in the respective folder, the DBCC CHECKDB still fail with the error as long as it does not have the READ permission to the root volume of the database files.

Microsoft article about the cause and its suggested solution by manually creating a snapshot and run DBCC CHECKDB on that snapshot (However, please note that database snapshot creation is only possible for Enterprise edition)