Microsoft KB Archive/928518

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

Article ID: 928518

Article Last Modified on 11/20/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
 * Microsoft SQL Server 2005 Enterprise X64 Edition
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Standard X64 Edition

-



Bug #: 489609 (SQLBUDT)



SYMPTOMS
In Microsoft SQL Server 2005, 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, the following message 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 &quot; &quot;.

Maintenance Plan: PlanForReadOnlyDb

Duration: 00:00:52

Status: Warning: One or more tasks failed..

Details:

Check Database Integrity Task

Check Database integrity on

Databases: AdventureWorks,msdb,PassDb,Sales,test1 Include indexes

Task start:.

Task end:.

Failed:(-1073548784) Executing the query &quot;DBCC CHECKDB WITH NO_INFOMSGS

&quot; failed with the following error: &quot;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.&quot; Possible failure reasons: Problems with the query, &quot;ResultSet&quot; 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 &quot;How to: Create a Database Snapshot (Transact-SQL)&quot; topic in SQL Server 2005 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 &quot;Applies to&quot; section.



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

To reproduce this problem, run the following commands in SQL Server 2005: -- 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: &quot;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.&quot;

-- 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 &quot;Symptoms&quot; section.

Keywords: kbtshoot kbprb kbexpertiseadvanced kbsql2005engine KB928518

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.