Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 953503 - Last Review: June 5, 2008 - Revision: 1.3
The DBCC CHECKDB statement does not raise exceptions in SQL Server when the DBCC CHECKDB statement detect a consistency error in the database
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.
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
| kbexpertiseadvanced kbtshoot kbprb KB953503 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please