You are currently offline, waiting for your internet to reconnect

The DBCC CHECKDB statement does not raise exceptions in SQL Server when the DBCC CHECKDB statement detect a consistency error in the database

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

SYMPTOMS
In Microsoft SQL Server, you use a TRY…CATCH construct to process exceptions that are raised from the DBCC CHECKDB statement. However, when the DBCC CHECKDB statement detects a consistency error in the database, the CATCH block is not triggered.

Note This behavior also occurs in SQL Server 2008.
CAUSE
This behavior occurs because the DBCC CHECKDB statement does not raise an exception when the DBCC CHECKDB statement detects a consistency error inside the database.
WORKAROUND
To work around this issue, manually raise a customized exception when the DBCC CHECKDB statement detects a consistency error. For example, use a statement that resembles the following:
BEGIN TRYDECLARE @Return INTEXEC @Return = sp_executesql N'DBCC CHECKDB(''<DBName>'')'IF @Return <> 0 RAISERROR ('Database corrupt', 11, 1)END TRYBEGIN CATCHSELECTERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage;END CATCH
STATUS
This behavior is by design.
MORE INFORMATION
When the DBCC CHECKDB statement detects a consistency error in the database, the DBCC CHECKDB statement records the error number and the error message of the consistency error. Additionally, the DBCC CHECKDB statement must detect all consistency errors inside the database. Therefore, the DBCC CHECKDB statement does not raise an exception when the DBCC CHECKDB statement detects the first consistency error so that the current execution is not interrupted.

If you execute the SELECT @@ERROR statement immediately after the DBCC CHECKDB statement, the SELECT @@ERROR statement returns the last consistency error that the DBCC CHECKDB statement detects.

The TRY…CATCH construct only intercepts exceptions that are raised from errors. Additionally, the CATCH block is triggered only if an exception is raised by an error that has a severity level between 10 and 20.
dbcc checkdb try catch trigger error exception database corruption
Properties

Article ID: 953503 - Last Review: 06/05/2008 22:13:19 - Revision: 1.3

Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2000 Personal Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000, Workgroup Edition, Microsoft SQL Server 2000 Enterprise Edition, Microsoft SQL Server 2000 Developer Edition

  • kbexpertiseadvanced kbtshoot kbprb KB953503
Feedback
&t=">