DBCC CHECKDB failing (Integrity Checks in maintenance plans) on SQL 2005 SP2

Posted on 2007-08-07
Last Modified: 2008-06-26
We just migrated our SQL 2000 production web database server to SQL 2005. All is well, and I'm setting up maintenance plans. I set up the Integrity checking to run weekly, but it fails with this error:

Failed:(-1073548784) Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
" failed with the following error: "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.

All databases are in 9.0 compatibility mode, and because this is the live SQL database to our website, there will always be connections to it at some time or another. I tried enabling SNAPSHOT ISOLATION on one particular database (ALTER DATABASE WebAnalysis  SET ALLOW_SNAPSHOT_ISOLATION ON), and then running the DBCC CHECK command, but get a similar failure. What am I missing?
Question by:havensms

    Author Comment

    This worked fine in SQL 2000, but if there's even one connection to the database, it won't run the DBCC.
    LVL 3

    Expert Comment


    Author Comment

    So, what do I do if I have the Standard Ed., and can't do snapshots? As my only option to do a restore to a different destination, and do it there?
    LVL 3

    Expert Comment

    In the cause there is a clause that says
    "      The database contains at least one file group that is marked as read-only.
    Is that your case?
    Obviously if you have a file group marked as read only you need it to be read only... or can you consider to make it not read only?
    No other ideas by now... sorry

    Author Comment

    All filegroups are R/W. Nothing is RO
    LVL 3

    Accepted Solution

    As far as I know, DBCC CHECKDB creates his own snapshot to make his checks EXCEPT when you run it with "TABLOCK" options that forces the DBCC to obtain exclusive access to the database (so if you have a connection opened, it fails)..

    Other cases when the snapshot is not created and the DBCC needs exclusive access to the db are (according to books on line):
    "An internal database snapshot is not created when a DBCC command is executed:

        * Against master, and the instance of SQL Server is running in single-user mode.
        * Against a database other than master, but the database has been put in single-user mode by using the ALTER DATABASE statement.
        * Against a read-only database.
        * Against a database that has been set in emergency mode by using the ALTER DATABASE statement.
        * Against tempdb. In this case, a database snapshot cannot be created because of internal restrictions.
        * Using the WITH TABLOCK option. In this case, DBCC honors the request by not creating a database snapshot.

    The DBCC commands use table locks instead of the internal database snapshots when the command is executed against the following:

        * A read-only filegroup
        * An FAT file system
        * A volume that does not support 'named streams'
        * A volume that does not support 'alternate streams'


    So if you are in one of this cases you need exclusive access to the db.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now