Can I run DBCC CHECKDB from .NET?

Question

I am writing a scheduled job to mimic a SQL Server maintenance plan for SQL Express. (I have to do this because SQL Agent and related tools don't exist for SQL Express)

One of the steps is to do a database integrity check. The TSQL for this is:

DBCC CHECKDB(N'Northwind')  WITH NO_INFOMSGS

How do I know if an error occurred during execution of this command, will it throw an Exception when using ADO.NET or would I have to parse the text output of the command (if so what do I look for in the output)

This is difficult to test because I don't have a corrupt database on hand.

Wichita

Answer 1

Yes I believe you would need to process the text output returned from DBCC CHECKDB.

To assist with your testing, the following reference details how to deliberately corrupt a SQL Server Database.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/03/10/how-to-create-a-corrupt-database-using-bulk-insert-update-and-bcp-sql-server-as-a-hex-editor.aspx

Ketron

Thanks for the URL on how to corrupt a DB, that is really useful. – Wichita

Answer 2

You can use the TABLERESULTS option with CHECKDB (DBCC CHECKDB WITH TABLERESULTS). That will give you a recordset with columns like Error, Level, State, MessageText (among many others).

The Level column (severity level) of that recordset should be enough to decide if there's any error.

MS says that levels 11 to 16 are "generated by the user, and can be corrected by the user". So I'd say anything above 17 should mean: stop making any backups (to avoid overwriting good backups with broken ones), take system offline if possible, and immediately notify an operator.

And levels 11 to 16 should probably be reported to the operator as well (through regular Email or whatever), so he can check into it if necessary. (I'm not sure if CHECKDB will ever report an error with levels 11 through 16 though. Having the code in there to log the error/notify an operator probably won't hurt though.)

NOTE: if you combine TABLERESULTS with NO_INFOMSGS, and if CHECKDB doesn't find any errors, you will not get any recordset as a result, not even one with no rows.

NOTE2: Under certain conditions, CHECKDB will just fail with an error code. So far I've only seen one error that triggers this, and it looks like this:

Msg 211, Level 23, State 51, Line 3
Possible schema corruption. Run DBCC CHECKCATALOG.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

I'm not using ADO.NET much, but I think ADO.NET will react by throwing an exception. Also, since this is an error with severity >= 20, it will cause the client connection to be closed.

---

To sum this all up: I'd run DBCC CHECKDB WITH TABLERESULTS. If the command fails, there is a problem (probably a severy one). If not, go on to loop through the result set, and look for any severity levels >= 17. If you find one, there probably is some kind of severe problem too.

Cogitation

Would like to add one comment, Severity level 10 messages are informational and indicate problems caused by mistakes in the information you have entered. Severity levels from 11 through 16 are generated by the user, and can be corrected by the user. So I would advise to check for Levels >= 11. And most errors actually will have level = 16 – Hypostatize

I already wrote that levels 11 to 16 should probably be reported to an operator. If he provides incorrect SQL, he will probably get an error in the range 11 ... 16. But then his job will always fail. If the syntax is correct though, I don't see how/why CHECKDB would ever report a "user correctable error". And even if it does, I'm 99% sure it won't be because the database is damaged. I really expect a level 21+ message in that case. – Cogitation