Error message when you run any of the DBCC CHECK commands in SQL Server: "The database could not be exclusively locked to perform the operation"

Applies to: Microsoft SQL Server 2005 Developer EditionMicrosoft SQL Server 2005 Enterprise EditionMicrosoft SQL Server 2005 Enterprise X64 Edition More

Bug #: 489609 (SQLBUDT)

Symptoms


In Microsoft SQL Server, you may receive an error message when you run any of the following DBCC commands:  
  • DBCC CHECKDB
  • DBCC CHECKTABLE
  • DBCC CHECKALLOC
  • DBCC CHECKCATALOG
  • DBCC CHECKFILEGROUP
The error message contains the following text:
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.

If you have scheduled maintenance plan tasks that perform database integrity checks, an error message that resembles the following may be written to the log file for the corresponding plan:

Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "ComputerName".
Maintenance Plan: PlanForReadOnlyDb
Duration: 00:00:52
Status: Warning: One or more tasks failed..
Details:
Check Database Integrity Task (ComputerName)
Check Database integrity on ComputerName
Databases: AdventureWorks,msdb,PassDb,Sales,test1 Include indexes
Task start:
DateTime.
Task end:
DateTime.
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.

Cause


This problem occurs if the following conditions are true:
  • At least one other connection is using the database against which you run the DBCC CHECK command.
  • The database contains at least one file group that is marked as read-only.
Starting with SQL Server 2005, DBCC CHECK commands create and use an internal database snapshot for consistency purposes when the command performs any checks. If a read-only file group exists in the database, the internal database snapshot is not created. To continue to perform the checks, the DBCC CHECK command tries to acquire an EX database lock. If other users are connected to this database, this attempt to acquire an EX lock fails. Therefore, you receive an error message.

Resolution


To resolve this problem, follow these steps instead of running the DBCC CHECK command against the database:
  1. Create a database snapshot of the database for which you want to perform the checks. For more information about how to create a database snapshot, see the "Create a Database Snapshot (Transact-SQL)" topic in SQL Server Books Online.
  2. Run the DBCC CHECK command against the database snapshot.
  3. Drop the database snapshot after the DBCC CHECK command is completed.

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

More Information


For more information about how the DBCC CHECK command uses the internal database snapshot, see the "DBCC (Transact-SQL)" topic under the "DBCC Internal Database Snapshot Usage" topic in SQL Server 2005 Books Online.

To reproduce this problem, run the following commands in SQL Server:
-- Open a new query that is named conn1, and then create a new database.
CREATE DATABASE DBTESTE3
GO
-- Add a new file group.
ALTER DATABASE DBTESTE3 ADD FILEGROUP FGTESTE
GO
-- Add a file to the new file group.
ALTER DATABASE DBTESTE3 ADD FILE (NAME=DBTESTE3_Data2, FILENAME='C:\DBTESTE3_Data2.ndf')
TO FILEGROUP FGTESTE
GO
-- Change the file group to read-only.
ALTER DATABASE DBTESTE3 MODIFY FILEGROUP FGTESTE READONLY
GO

-- Run the DBCC CHECK command in the conn1 query.
DBCC CHECKDB (DBTESTE3)

-- The DBCC CHECK command runs correctly. However, you may receive the following message:
"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."

-- Open a new query window that is named conn2, and then set the database as DBTESTE3. This action opens a connection to the DBTESTE3 database.

-- Return to the conn1 query, and run the DBCC command again.

DBCC CHECKDB (DBTESTE3)
-- Notice the error message that is mentioned in the "Symptoms" section.