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.
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.
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
This behavior is by design.
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.