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

Article translations Article translations
Article ID: 953503 - View products that this article applies to.
Expand all | Collapse all

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 TRY
DECLARE @Return INT
EXEC @Return = sp_executesql N'DBCC CHECKDB(''<DBName>'')'
IF @Return <> 0 RAISERROR ('Database corrupt', 11, 1)
END TRY
BEGIN CATCH
SELECT
ERROR_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.

Properties

Article ID: 953503 - Last Review: June 5, 2008 - Revision: 1.3
APPLIES TO
  • 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
Keywords: 
kbexpertiseadvanced kbtshoot kbprb KB953503

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com