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.

Answer 1

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.