DBCC CHECKDB error. Internal Database Snapshot cannot be created.

We recently migrated our production server from SQL 2005 (Standard) on Win2003(32-bit) to SQL2012 (Standard; v11.0.3000) on Win2008-R2(64bit). Single-server Dell R510 with 1.2TB storage. Everything went smoothly; the only nagging issue remaining is failure of our maintenance jobs.  I tracked the issue down to failure of DBCC CHECKDB.  Specifically, the error is:

Executed as user: NT SERVICE\SQLSERVERAGENT.  The database could not be exclusively locked to perform the operation. [SQLSTATE 42000] (Error 5030) 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

I have Googled this issue and read extensively. For instance, informative blogs (albeit dated) such as these by Paul Randal (Managing Director, SQLskills.com):

www.sqlskills.com/blogs/paul/issues-around-dbcc-checkdb-and-the-use-of-hidden-database-snapshots/

www.sqlskills.com/blogs/paul/checkdb-from-every-angle-complete-description-of-all-checkdb-stages/

As others have noted:

CheckDB by default takes an internal DB snapshot to get the consistent, point-in-time view of the DB that it needs. If that snapshot creation fails, then it will try to get an exclusive database lock before proceeding (same as if you had executed DBCC CHECKDB WITH TABLOCK). The root problem is not that the lock could not be obtained, it's that the internal database snapshot could not be created.

msdn.microsoft.com/en-us/library/ms188796.aspx details the specific situations when an internal database snapshot is not created and table locking is attempted.  None of these situations apply.  Which only leaves me with the permissions issue discussed in Paul Randals blog referenced above.  I have verified the SQLSERVERAGENT service account has full permissions on the SQLDATA directory where the databases reside and has full permissions on each database within the directory. Just for giggles, I created a job (run as SQLSERVERAGENT) that creates and then deletes a text file in the SQLDATA directory.  It runs fine.

Also tested I get the snapshot creation error when manually running DBCC CHECKDB against any of our databases and when executing under a variety of administrator accounts that are members of the SQL sysadmin role and the Domain Admins security group (the Domain Admins is a member of the local Administrators group that has full permissions on all SQL directories/folders).

Additionally, the databases in question are small (200MB to 6GB) and the disk has plenty of elbow room (978GB free on 1.22TB RAID5 array) to create the internal database snapshots.

So, I am stumped CHECKDB doesn't surface an error message that is detailed enough to determine the precise cause of the error.  I would really like to hear from someone who is successfully running DBCC CHECKDB on the SQL2012 (Standard) on a Win2008 R2 (64-bit) server.

Thanks!

August 22nd, 2013 2:03pm

Dear Tom,

Could you please check your maintenance plan script if it is using TABLOCK for master DB?

I have tried the below:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
    Dec 28 2012 20:23:12
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

DBCC CHECKDB('CUSTOM DB') WITH TABLOCK- SUCCESS

DBCC CHECKDB('master') WITH TABLOCK -ERROR

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.

DBCC CHECKDB('master')- Success

Due to security reason , I tried in my development environment.

Please share your outcome.

Free Windows Admin Tool Kit Click here and download it now
August 22nd, 2013 2:33pm

Hi,

What compatibility level are the databases in?  Try running DBCC UPDATEUSAGE.  

Does it work on any newly created databases, i.e. databases that didn't get upgraded with SQL Server 2005?  Try creating a brand new database and run DBCC CHECKDB against that.

Can you manually create a snapshot of any of the databases you are having problems with?

Can you try putting a database in single user mode to run DBCC CHECKDB with tablock?

August 22nd, 2013 2:35pm

Additionally my current user has local admin role.
Free Windows Admin Tool Kit Click here and download it now
August 22nd, 2013 2:35pm

Hello Tom,

It seems to me as Permission issue to me (not quite sure) but once i faced this issue .I specifically added sql server service account in Security tab of folder  and tried runnig CHEKCDB again I suceeded.If your account is domain such problems might come

Problem here is Snapshot is not getting created.

You can also try suggesstion here http://blogs.msdn.com/b/karthick_pk/archive/2010/03/07/dbcc-checkdb-fails-the-database-could-not-be-checked-as-a-database-snapshot-could-not-be-created-and-the-database-or-table-could-not-be-locked.aspx

If none of this works and your are sure your Service account has correct permission than starting SQL server service should also be your option after restart try again.But this should be last option.

August 22nd, 2013 2:55pm

Taherul I,

Thanks for the reply.  Sorry, I should have been more specific in my initial post.  I am not trying to run CHECKDB on any of the system databases: master, msdb, or tempdb.  These will of course fail if TABLOCK is specified because you will never be able to get an exclusive lock on these DBs.  The ability to run CHECKDB on these system databases (with/without snapshots) is well documented.

Thanks.


  • Edited by Tom H-man Thursday, August 22, 2013 6:48 PM
Free Windows Admin Tool Kit Click here and download it now
August 22nd, 2013 6:13pm

Andrew,

Thanks for your reply. To answer your questions...

Databases are in SQL2012(110) compatibility level.  DBCC UPDATEUSAGE executed without any error.

Issue occurs with every database (FYI, I'm not trying to run it on system dbs: master, msdb, tempdb)

I created a brand new database and added table with test data.  DBCC CHECKDB fails with same error.  If I detach and reattach the DB (to ensure no connections), DBCC CHECKDB completes with the following output.  Clearly, the issue is with its inability to create the internal database snapshot.

DBCC results for 'TomTest_8_22_2013'.
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 results for 'sys.sysrscols'.
There are 872 rows in 12 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 125 rows in 1 pages for object "sys.sysrowsets".

[clip]

CHECKDB found 0 allocation errors and 0 consistency errors in database 'TomTest_8_22_2013'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


I cannot manually create a snapshot of the database because SQL2012 Standard edition doesn't support snapshots (it does support internal database snapshots and this feature worked fine in our SQL2005 Standard environment).

I can successfully run DBCC CHECKDB WITH TABLOCK on databases when there are no connections.  Results are same as shown above (i.e. "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")

 

August 22nd, 2013 6:35pm

Hello Shanky,

Thanks for the reply.  As I noted in my original post, the MSSQLSERVER and SQLSERVERAGENT accounts have full permissions on the SQLDATA directory.  From a job running as SQLSERVERAGENT, I can successfully read/write a file to the directory where all the databases reside.  I have already read the information at the link you provided and it is only a parrot of what is available on the msdn link I provided and on Paul Randal's blog (he was the author of the DBCHECK utility when on the MS SQL development team).

My SQL server has been restarted multiple times for other reasons.  THis issue still persists.

Thanks again.  I appreciate all responses.


  • Edited by Tom H-man Thursday, August 22, 2013 6:49 PM
Free Windows Admin Tool Kit Click here and download it now
August 22nd, 2013 6:45pm

can you check sys.databases to see whether there are any orphaned snapshots?  How are you restarting SQL Server - via the configuration manager?  You're NTFS file system, right?
August 23rd, 2013 2:47pm

Thanks Andrew for hanging in there with me on this issue...

The hidden (internal) database snapshots created with CHECKDB are not visible in the sys.databases, sys.master_files, or other system catalogs and the snapshot creation does not trigger server level events for a DLL trigger nor the database create or database start events for SQL Trace or Extended Events. The lack of detailed events is the reason this seemingly simple issue has been such a bug-a-boo.  To follow through with your advice, I did check the sys.databases table anyway, and nothing unusual there.

To restart my SQL server, I ensure no active connections, then stop all of the SQL services.  I do this either through Configuration Manager or by running the services.msc. I then reboot the server as normal and all SQL services start as expected.

Definitely NTFS on our 1.2TB RAID5 array.

The SQL 2012 Standard Ed doesn't support manually creating snapshots, otherwise I could simply create a snapshot and run CHECKDB against it.  This would likely expose the reason the hidden snapshots are failing, but, alas, not an option for me. Unless this is a bug, something in the back of my head keeps telling me it is a fundamental issues such as permissions. But as I noted above, I have checked that angle.  As the lead for our IT Team, our entire network is under my control: I can log into SSMS as a domain admin that is also a member of the sysadmin role and can change NTFS folder security at will. In addition, I have tried executing CHECKDB in jobs with/without SQL proxies and using the SQL virtual accounts (SQLSERVERAGENT, MSSQLSERVER). 

Running DBCC CHECKDB is such a fundamental task that I'm finding it hard to believe others have not run into this issue. But I have searched and spent way too many hours testing... Never had a problem running maintenance jobs on our SQL2005 environment.  That's progress, eh?

Thanks again,

Tom

Free Windows Admin Tool Kit Click here and download it now
August 23rd, 2013 9:28pm

Hi - no problem.  Stumped as you are... Was following up on a post from Paul R I saw regarding orphaned snapshots.

Do you have a test system with a similar build?   Somewhere you can compare results against?

I say through configuration manager, as I'm sure you're aware, registry hives/keys, etc. permissions might get affected during a sql service restart which would only be affected through the configuration manager - services.msc only restarts the service.

I completely agree - I'm leaning towards permissions.  Is this a box you can play with?  Can you change the service account that SQL Server is running under - to a local admin?  Just to rule it out?

Perhaps create a database in local folder (a location other than the default directory) with everyone - full control on it?

August 23rd, 2013 9:59pm

Andrew,

Thanks for the follow-up.  I've been continuing to pick-away at this problem in my spare time.  It isn't a show stopper for us, but I really would like our maintenance jobs to run successfully.

The box in question is currently our production server, so I can only play around with permissions in a limited way (i.e. understandably reluctant to change permissions on the fundamental services!)

I could spin up a clean install of SQL2012 on our hyper-v host and test using domain-level accounts on the SQL services as was the case for our SQL2005 environment.  But, I'm not at the point yet where I want to invest that kind of effort.

I already had tried your last suggestion (created a clean database in folder other than SQLDATA, created a test database and log in the folder, provided Everyone full control on the folder and database, and manually ran DBCC CHECKDB while logged into the SSMS as a Domain Admin that is a member of the local administrators group and also is a member of the SQL sysadmin role.  Also verified that Administrators group has been assigned to the appropriate policies under Local Computer Policy\...\User Rights Assignment.  That's pretty much god-like privileges over SQL and the server OS.

At this point I am going to let this one go...

Again, thanks for all the suggestions.

- Tom

Free Windows Admin Tool Kit Click here and download it now
August 27th, 2013 2:07pm

This is an aging post but just on the outside chance a fellow DBA's search engine happens to find it...

I had the same problem and was able to find the solution.  Knowing that the DBCC commands will run in the security context of whatever account the engine is using, and that my 60 other database servers were having no trouble executing them, combined with the fact that permissions settings on both the root drive folder and the subfolders where the data and log files lived seemed a bit wonky (I hadn't set this one up), I was pretty well convinced at the outset that this was a problem with NTFS permissions.  Specifically, SQL Server couldn't create the snapshot because it didn't have permissions to create the files for it.

The solution wasn't quite as straightforward as you might think so I thought I should post here and maybe save some folks some time.  Setting full control on the data and log folders for the SQL service account wasn't enough.  The problem was in the permissions set for the data and log files themselves.  I noticed that whenever I went into the Advanced security settings on either the data or log folder and ticked the checkbox to "replace all child object permissions..." I would get "Access denied" as the commands hit each of the data or log files.  I honestly don't recall what permissions were set on the individual files, but I knew they wouldn't propagate from the parent folder and that was a problem.

At first I thought it was simply because these files belonged to online, active databases but that didn't turn out to be the case.  The workaround was to temporarily set myself (my AD account) as the owner of the parent folder, and set the checkbox to replace the owner of all child objects.  I then closed all dialog boxes to set the changes in effect (note that you must have local admin rights on the box to be able to do this).  I then went back into Advance permissions on the folder and checked the box to replace all child object permissions.  Now all folder permissions propagated to files without error.  Finally, I set the owner of the folder (and all child objects) back to the Local Administrators group.  DBCC CHECKDB now completed successfully on all databases.

Note that I didn't try to log into the box with the local Administrator account.  If I had I may have been able to bypass the need to take ownership of the folder and files.  The upshot is that you have to make sure the permissions set for the data and log folders are correct, and that the data and log files have properly inherited those permissions.

  • Proposed as answer by Shanky_621MVP Thursday, September 04, 2014 3:27 PM
  • Unproposed as answer by Shanky_621MVP Thursday, September 04, 2014 3:28 PM
August 7th, 2014 5:00pm

Even though my original post was over a year ago, I appreciate the reply. We have not been able to solve the problem and I hate getting the weekly email message that the maintenance job has failed. :-/

Our problem is not a simple permissions issue as you discovered for your environment.  On our production SQL server we only have 15 databases on a separate data volume.  The data volume contains the SQLDATA and SQLLOGS folders.  I have Domain Administrator access to the server.  The Domain Administrator is a member of the local Administrators group.  The Administrators group and SYSTEM account have Full Control permissions of the volume.  Those permissions are inherited by the SQLDATA and SQLLOGS folders and all child objects.  The Local Administrator group is also the owner of those folders and all child objects.  In addition, the SQL virtural accounts NT SERVICE\MSSQLSERVER and NT SERVICE\SQLSERVERAGENT have Full Control permissions on the SQLDATA and SQLLOGS folders and all child objects.  There are no permissions issues (inheritance) with child objects as you experienced in your environment.

Thanks again,

Tom

I welcome all replies.  If I need to repost so the topic is current, please let me know.

Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2014 5:30pm

Does your service account belong to sysadmin fixed server role or the db_owner fixed database role?
September 3rd, 2014 6:04pm

I appreciate the continued interest in this issue.

The service account belongs to the sysadmin fixed server role

Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2014 11:10pm

To rule out that it is a folder/file level permission issue, try manually creating a snapshot on the database. If the manual snapshot succeeds, then we can at least deduce that is a folder/file level permission issue with CHECKDB trying to create it in default folder. If the manual snapshot fails, then it is some other internal issue - it could be a meta corruption of the some of the objects in the databases - rebuilding all indexes also fixes some weird issues with DBCC CHECKDB (not just index corruption issues) 
September 4th, 2014 12:21am

Unfortunately, I cannont perform that test.  Manually creating a database snapshot requires Enterprise edition.  We have the Standard edtion. CheckDB by default takes an internal DB snapshot; these are supported in Standard edition. 

Free Windows Admin Tool Kit Click here and download it now
September 4th, 2014 3:08pm

Do you have another server running Enterprise or Developer Edition? If so, you can restore a copy of this database to that server and try to run CHECKDB. If it succeeds, then we know it has to do something with file/folder permissions on the current server. If it fails, you can manually try to create the snapshot?
September 4th, 2014 3:16pm

Hi Tom,

We are also getting the same error with DBCC CHECKDB. Since you had the problem in 2013 (and now we are in 2015), have you resolved the issue yet? If you were able to resolve, would you please share with us how you fix the problem?

Thanks in advance,

Jimmy

Free Windows Admin Tool Kit Click here and download it now
May 7th, 2015 3:35pm

Greetings Tom,

In case you're still monitoring this, I had this issue about just now and see if this article: https://connect.microsoft.com/SQLServer/feedback/details/798675/2008-r2-engine-dbcc-checkdb-fails-against-databases-on-drives-that-dont-have-certain-root-permissions helps you.

It helped me.

Cheers.

July 19th, 2015 9:44pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics