0

We have a DBCC CHECKDB job that runs weekly on Saturdays. This job was created thorugh Maintenance Plan wizard. Typical duration for this job is 1.5 hours. This past weekend the job failed with the error below:

Executing the query "DBCC CHECKDB(N'DB_1')  WITH NO_INFOMSGS
failed with the following error: "A database snapshot cannot be created because it failed to start.
A database snapshot cannot be created because it failed to start.
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'G:\\_____'.
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.
The database could not be exclusively locked to perform the operation.
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.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Background: This error is occurring on two databases (DB_1 and DB_2). On Monday, DB_1 and DB_2 data .mdf files were moved from I: drive to G: drive to provide temporary relief of disk space running out on I: drive.

When the data files were moved, I ensured that my user account and SQL service account has full control. So I am not sure why the DBCC job is failing. There are no corruption or consistency errors that were noted after the checks completed.

Currently the G drive has 116GB of free space left.

DB_1 data file is 47GB.

DB_2 data file is 27GB.

What I did: I restored a full backup of DB_1 and DB_2 on our UAT server to re run CheckDB again, to ensure that there are no severe corruption/consistency errors. I researched this error and most articles provide a solution to add full control for the SQL service account. Which in my case the SQL service account already has sufficient permission.

If anyone has experienced this error before and has found a way to resolve it, please advise. TIA

2
  • When you granted permission to the filesystem on the G drive, at what level did you grant the permissions? to the files directly? Or to the directory containing the data files? Also, are the files within a directory, or are they placed directly on the root of the G drive?
    – AMtwo
    Oct 18, 2021 at 14:53
  • @AMtwo The data files are located in G:\DATA\. So the files are within a directory. I gave SQL service account full control in the DB_1.mdf and DB_2.mdf file. Do I need to give SQL Service account full control on \DATA\ directory? Oct 18, 2021 at 15:08

1 Answer 1

1

As @Charlieface mentioned in the comments, a part of CHECKDB is creating a snapshot to provide a point in time picture of your database file while checking for consistency. This snapshot is always in the same directory where the database files are located, so this is why your job is getting the error; the account doesn't have permissions to create a new file in that same location.

From BOL:

Internal Database Snapshot DBCC CHECKDB uses an internal database snapshot for the transactional consistency needed to perform these checks. This prevents blocking and concurrency problems when these commands are executed. For more information, see View the Size of the Sparse File of a Database Snapshot (Transact-SQL) and the DBCC Internal Database Snapshot Usage section in DBCC (Transact-SQL). If a snapshot cannot be created, or TABLOCK is specified, DBCC CHECKDB acquires locks to obtain the required consistency. In this case, an exclusive database lock is required to perform the allocation checks, and shared table locks are required to perform the table checks. DBCC CHECKDB fails when run against master if an internal database snapshot cannot be created. Running DBCC CHECKDB against tempdb does not perform any allocation or catalog checks and must acquire shared table locks to perform table checks. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be obtained. In Microsoft SQL Server 2012 or an earlier version of SQL Server, you may encounter error messages when you run the DBCC CHECKDB command for a database that has its files located on an ReFS-formatted volume. For more information, see Knowledge Base article 2974455: DBCC CHECKDB behavior when the SQL Server database is located on an ReFS volume.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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