Microsoft KB Archive/926070

From BetaArchive Wiki
Knowledge Base


Error messages may be logged in the SQL Server error log after you run a DBCC command in SQL Server 2005

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: 

2006-09-01 17:33:24.48 spid54      35 transactions rolled forward in database 'ProductionData' (11). This is an informational message only. No user action is required.
2006-09-01 17:35:39.16 spid54      4 transactions rolled back in database 'ProductionData' (11). This is an informational message only. No user action is required.
2006-09-01 17:36:31.76 spid53      Error: 17053, Severity: 16, State: 1.
2006-09-01 17:36:31.76 spid53      E:\SQLData\ProductionData.mdf:MSSQL_DBCC11: Operating system error 112(There is not enough space on the disk.) encountered.
2006-09-01 17:36:31.76 spid53      Error: 17053, Severity: 16, State: 1.
2006-09-01 17:36:31.76 spid53      E:\ SQLData \ProductionData.mdf:MSSQL_DBCC11: Operating system error 112(There is not enough space on the disk.) encountered.
2006-09-01 17:36:31.77 spid53      Error: 17053, Severity: 16, State: 1.
2006-09-01 17:36:31.77 spid53      E:\ SQLData \ProductionData.mdf:MSSQL_DBCC11: Operating system error 112(There is not enough space on the disk.) encountered.
2006-09-01 17:36:31.80 spid54      DBCC CHECKDB (ProductionData) executed by DomainName\UserName found 0 errors and repaired 0 errors. Elapsed time: 0 hours 3 minutes 19 seconds.
2006-09-01 17:36:31.90 spid53      Error: 17053, Severity: 16, State: 1.
2006-09-01 17:36:31.90 spid53      E:\ SQLData \ProductionData.mdf:MSSQL_DBCC11: Operating system error 112(There is not enough space on the disk.) encountered.
2006-09-01 17:36:31.90 spid53      Error: 17053, Severity: 16, State: 1.
2006-09-01 17:36:31.90 spid53      E:\ SQLData \ProductionData.mdf:MSSQL_DBCC11: Operating system error 112(There is not enough space on the disk.) encountered.
2006-09-01 17:36:32.30 spid54      Error: 926, Severity: 21, State: 6.
2006-09-01 17:36:32.30 spid54      Database 'ProductionData' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

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:

Msg 5128, Level 17, State 2, Line 6
Write to sparse file 'E:\CreateFile\ProductionData.mdf:MSSQL_DBCC11' failed due to lack of disk space.

In this case, the client application that runs the DBCC commands will have the following entries in the application result set:

DBCC results for 'ProductionData'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'ProductionData'.
Msg 926, Level 21, State 6, Line 1
Database 'ProductionData' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

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:

Msg 1823, Level 16, State 1, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
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.
Msg 5106, Level 17, State 2, Line 1
Write to sparse file 'E:\Data\LogFUllTest_Data.mdf:MSSQL_DBCC10' failed due to lack of disk space.

Keywords: kbtshoot kbprb kbexpertiseadvanced kbsql2005engine KB926070



Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.


Retrieved from ‘https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/926070&oldid=224412’
the Creative Commons 3.0 ShareAlike (except the Microsoft KB Archive).
Powered by MediaWiki