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