The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

Knowledge Base

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

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 "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:

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 "How to: Create a Database Snapshot (Transact-SQL)" topic in SQL Server 2005 Books Online.
Run the DBCC CHECK command against the database snapshot.
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 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:
"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.

Keywords: kbtshoot kbprb kbexpertiseadvanced kbsql2005engine KB928518

Microsoft KB Archive/928518

Contents