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?