Geek Answers Handbook

Can I run DBCC CHECKDB from .NET?

I am writing a scheduled task to simulate a SQL Server maintenance plan for SQL Express. (I have to do this because SQL Agent and related tools do not exist for SQL Express)

One step is to verify the integrity of the database. TSQL for this:

DBCC CHECKDB(N'Northwind') WITH NO_INFOMSGS

How to find out if an error occurred during the execution of this command, whether it throws an exception when using ADO.NET, or will I have to analyze the output of the command text (if so, what I'm looking for at the output)

This is difficult to verify because I do not have a damaged database.

+4
source share
2 answers

Yes, I believe that you need to handle the text output returned from DBCC CHECKDB.

To help with testing, the following help information describes how to intentionally ruin 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

+3
source

You can use the TABLERESULTS option with CHECKDB ( DBCC CHECKDB WITH TABLERESULTS ). This will give you a set of records with columns such as Error , Level , State , MessageText (among many others).

The Level column (severity level) of this record set should be sufficient to decide if there is any error.

MS says levels 11 through 16 are "user generated and can be fixed by the user." Therefore, I would say that something above 17 should mean: stop making backups (so as not to overwrite good backups with broken ones), if necessary, turn off the system offline and immediately inform the operator.

And levels 11 through 16 should probably also be communicated to the operator (via regular email or something else), so he can check it if necessary. (I'm not sure that CHECKDB will ever report an error with levels 11-16. Having a code to register an error / operator notification will probably not hurt.)

NOTE : if you combine TABLERESULTS with NO_INFOMSGS , and if CHECKDB does not detect errors, you will not get any recordset, even if one of them is not a row.

NOTE 2 . Under certain conditions, CHECKDB will simply fail with an error code. So far I have only seen one error that triggers this, and 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 do not use ADO.NET much, but I think that ADO.NET will respond by throwing an exception. In addition, since this is a bug with severity> = 20, this will close the client connection.

To summarize, I would do DBCC CHECKDB WITH TABLERESULTS . If the command fails, a problem occurs (possibly one of them). If not, continue through the result set and find the severity levels> = 17. If you find it, there is probably a serious problem.

+2
source

More articles:


All Articles