Issues around DBCC CHECKDB and the use of hidden database snapshots

There are a couple of issues that I’ve heard of in the last few weeks (one while onsite at a customer) and I think they might bite some people so I’d like to share them with you.

DBCC CHECKDB in 2005 onwards uses a hidden database snapshot to create the transactionally-consistent point-in-time view of the database that it requires to run the consistency checks. The hidden database snapshot is created as a set of NTFS alternate streams on the existing database data files. The alternative to having DBCC CHECKDB do this automatically is to manually create your own database snapshot and run DBCC CHECKDB against that – it’s the same thing really.

[Edit 6/19/14: From SQL Server 2014 onward, the database snapshot is not hidden and does not use NTFS alternate streams.]

More info on DBCC CHECKDB’s use of snapshots, and potential problems can be found at:

The two issues that I’ve heard of both are around an inability of DBCC CHECKDB to create the hidden snapshot. In that case it is forced to use locks to stabilize the database, which usually fails because the exclusive database lock required for running the allocation checks portion cannot be acquired.

The first issue is around the permissions of the SQL Server service account. To be able to create the NTFS alternate streams, the service account must have the privileges to create files in the DATA directory of the SQL Server instance. This is a really difficult problem to track down as the actual NTFS failure message is not surfaced by the snapshot creation code.

The second issue is around the use of HP PolyServe. Upgrading to Matrix Server 3.6.1 disables support for alternate streams in the filesystem, effectively breaking DBCC CHECKDB. Here’s the paragraph from the 3.6.1 upgrade guide (available here):

In previous releases, MxDB for SQL Server provided ADS support internally for use with various SQL Server features such as the DBCC CHECKDB command. This internal support has been removed in HP PolyServe Software for Microsoft SQL Server. Instead, after all servers are upgraded to 3.6.1, you will need to enable ADS support on all filesystems previously used with MxDB for SQL Server. During the upgrade to 3.6.1, SQL Server operations requiring ADS will fail, as the new ADS support feature is not yet in place on the nodes running 3.6.1. For continuity of SQL Server operations, it is important to upgrade all nodes to 3.6.1 and upgrade filesystems for ADS as quickly as possible.

Enabling support after the upgrade means running the PolyServe psfscheck command (which I believe just runs the NTFS fsutil command under the covers), which unfortunately means taking the volume momentarily offline.

Hope this helps!

9 thoughts on “Issues around DBCC CHECKDB and the use of hidden database snapshots

  1. Hello Paul!
    I’m testing with SQL Server 2014 RTM. Where should the checkdb snapshot be exposed now that its no longer hidden? During checkdb sys.dm_exec_requests shows a database_id that is not listed in sys.databases or sys.sysdatabases. The DB_NAME function does return the same name for this snapshot database as it does for the underlying base database.
    Thanks!

  2. Good Morning Paul,
    We have a DBCC CHECKDB job that runs every sunday. When it runs, it does CHECKDB only on 2 databases and fails. When we re-run the job, it does CHECKDB on all databases and job completes successfully. This is happening from last 3 weekends. Could you please let me know what could be the issue/reason it is failing the first time.

  3. Hi Paul,

    Need advise for DBCC snapshot file for SQL 2014

    DBCC job is scheduled to run every day. On 9 Oct the DBCC job failed. we re-ran the job and it completed successfully. On 9 OCT run the DBCC job created a snapshot file of one the database as “XXXX.mdf_MSSQL_DBCC20”. We tried to delete the file but throws error as “File in use by SQL Server”. No errors found in logs and the Database is online.

  4. Paul, I have recently had 2 MSSQL isntances, one 2008 R2 and one 2014, where the dbcc checkdb job runs for days and never completes. I’ve had to kill the job the last 3 weeks. Can you give me any idea of what the issue is or where to research\troubleshoot this?

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Some thoughts on courage

(This is also the Ponderings – editorial – in today’s SQLskills newsletter.) I want to start out this post by sincerely thanking everyone who emailed

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.