Article ID: 926070
Article Last Modified on 11/20/2007
APPLIES TO
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Workgroup Edition
- Microsoft SQL Server 2005 Express Edition
- Microsoft SQL Server 2005 Standard X64 Edition
- Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
- Microsoft SQL Server 2005 Enterprise X64 Edition
- Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
SYMPTOMS
You run one of the following DBCC commands in Microsoft SQL Server 2005:
- DBCC CHECKDB
- DBCC CHECKALLOC
- DBCC CHECKTABLE
- DBCC CHECKCATALOG
- DBCC CHECKFILEGROUP
After you do this, error messages that resemble the following may be logged in the SQL Server error log:
CAUSE
In SQL Server 2005, DBCC commands use internal read-only database snapshots. These database snapshots are created on the same drive where the corresponding database data files are located. Database snapshots grow in proportion to the amount of changed data in the database against which the DBCC commands run. If transactional activity continues on this database, the database snapshots that are created by DBCC commands may experience disk space issues. Because the database snapshot files and the actual data files reside on the same disk drive, both sets of files compete for disk space. In this case, application transactions or user transactions are given preference. Therefore, the DBCC commands experience errors and cannot finish.
STATUS
This behavior is by design.
MORE INFORMATION
The following important information applies to the error messages that are mentioned in the "Symptoms" section:
- These error messages are from two different active server process identifiers (SPIDs). SPID 54 is the session ID that executes the DBCC command. SPID 53 is the session ID that executes a user transaction.
- These error messages indicate rolling forward transactions and rolling back transactions. These messages are generated during the initial phase of DBCC command execution. When you run a DBCC command, the DBCC command first tries to create an internal snapshot. When the snapshot is created, database recovery is performed against this snapshot to bring the snapshot into a consistent state. The error messages reflect this activity.
- Error message 926 indicates that the database is marked as suspect. This error message actually refers to the internal snapshot and not to the actual database. The status of the database is "online," and the database is functional.
- Error message 17053 contains the name of the NTFS file system alternate streams that are used for the internal snapshot. This error message indicates the real reason for the problem.
- The internal database snapshot uses the same name as the actual database. Therefore, these error messages contain the name of the database.
For more information about this problem, see the following topics in SQL Server 2005 Books Online:
- DBCC Internal Database Snapshot Usage
- Understanding Sparse File Sizes in Database Snapshots
Follow steps that are documented in these topics to avoid space usage issues. After you correct any problem, rerun the DBCC commands.
In addition to the error messages that are mentioned in the "Symptoms" section, you may receive the following error message:
In this case, the client application that runs the DBCC commands will have the following entries in the application result set:
If the snapshot could not be created at all, you receive error messages that resemble the following in the client application that issues the DBCC commands:
Keywords: kbtshoot kbprb kbexpertiseadvanced kbsql2005engine KB926070