• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1351
  • Last Modified:

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

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?
0
havensms
Asked:
havensms
  • 3
  • 3
1 Solution
 
havensmsAuthor Commented:
This worked fine in SQL 2000, but if there's even one connection to the database, it won't run the DBCC.
0
 
ibrusettCommented:
0
 
havensmsAuthor Commented:
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?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ibrusettCommented:
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
0
 
havensmsAuthor Commented:
All filegroups are R/W. Nothing is RO
0
 
ibrusettCommented:
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'

Reference: http://technet.microsoft.com/en-us/library/ms188796.aspx

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now