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)
我正在编写一个计划作业来模仿SQL Express的SQL Server维护计划。 (我必须这样做,因为SQL Express不存在SQL代理和相关工具)
One of the steps is to do a database integrity check. The TSQL for this is:
其中一个步骤是进行数据库完整性检查。这个TSQL是:
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)
我如何知道在执行此命令期间是否发生错误,是否在使用ADO.NET时抛出异常,或者我是否必须解析命令的文本输出(如果是,我在输出中查找什么)
This is difficult to test because I don't have a corrupt database on hand.
这很难测试,因为我手边没有损坏的数据库。
2 个解决方案
#1
Yes I believe you would need to process the text output returned from DBCC CHECKDB.
是的我相信您需要处理从DBCC CHECKDB返回的文本输出。
To assist with your testing, the following reference details how to deliberately corrupt a SQL Server Database.
为了帮助您进行测试,以下参考详细介绍了如何故意破坏SQL Server数据库。
#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).
您可以在CHECKDB中使用TABLERESULTS选项(DBCC CHECKDB WITH TABLERESULTS)。这将为您提供一个记录集,其中包含Error,Level,State,MessageText(以及许多其他列)。
The Level column (severity level) of that recordset should be enough to decide if there's any error.
该记录集的Level列(严重性级别)应足以确定是否存在任何错误。
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.
MS表示11到16级是“由用户生成的,可由用户更正”。所以我要说17以上的任何东西应该意味着:停止进行任何备份(以避免用破坏的备份覆盖好的备份),尽可能使系统脱机,并立即通知操作员。
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.)
并且应该向操作员报告11到16级(通过常规电子邮件或其他),因此他可以在必要时进行检查。 (我不确定CHECKDB是否会报告级别11到16的错误。但是在那里记录错误/通知操作员的代码可能不会受到伤害。)
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.
注意:如果您将TABLERESULTS与NO_INFOMSGS结合使用,并且如果CHECKDB没有找到任何错误,那么您将不会获得任何记录集,即使没有行也没有。
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:
注意2:在某些情况下,CHECKDB将失败并显示错误代码。到目前为止,我只看到一个触发此错误的错误,它看起来像这样:
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.
我没有太多使用ADO.NET,但我认为ADO.NET会通过抛出异常来做出反应。此外,由于这是严重性> = 20的错误,因此将导致客户端连接关闭。
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.
总而言之:我将运行带有TABLERESULTS的DBCC CHECKDB。如果命令失败,则存在问题(可能是一个问题)。如果没有,继续循环遍历结果集,并查找任何严重性级别> = 17.如果找到一个,可能还存在某种严重问题。
#1
Yes I believe you would need to process the text output returned from DBCC CHECKDB.
是的我相信您需要处理从DBCC CHECKDB返回的文本输出。
To assist with your testing, the following reference details how to deliberately corrupt a SQL Server Database.
为了帮助您进行测试,以下参考详细介绍了如何故意破坏SQL Server数据库。
#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).
您可以在CHECKDB中使用TABLERESULTS选项(DBCC CHECKDB WITH TABLERESULTS)。这将为您提供一个记录集,其中包含Error,Level,State,MessageText(以及许多其他列)。
The Level column (severity level) of that recordset should be enough to decide if there's any error.
该记录集的Level列(严重性级别)应足以确定是否存在任何错误。
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.
MS表示11到16级是“由用户生成的,可由用户更正”。所以我要说17以上的任何东西应该意味着:停止进行任何备份(以避免用破坏的备份覆盖好的备份),尽可能使系统脱机,并立即通知操作员。
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.)
并且应该向操作员报告11到16级(通过常规电子邮件或其他),因此他可以在必要时进行检查。 (我不确定CHECKDB是否会报告级别11到16的错误。但是在那里记录错误/通知操作员的代码可能不会受到伤害。)
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.
注意:如果您将TABLERESULTS与NO_INFOMSGS结合使用,并且如果CHECKDB没有找到任何错误,那么您将不会获得任何记录集,即使没有行也没有。
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:
注意2:在某些情况下,CHECKDB将失败并显示错误代码。到目前为止,我只看到一个触发此错误的错误,它看起来像这样:
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.
我没有太多使用ADO.NET,但我认为ADO.NET会通过抛出异常来做出反应。此外,由于这是严重性> = 20的错误,因此将导致客户端连接关闭。
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.
总而言之:我将运行带有TABLERESULTS的DBCC CHECKDB。如果命令失败,则存在问题(可能是一个问题)。如果没有,继续循环遍历结果集,并查找任何严重性级别> = 17.如果找到一个,可能还存在某种严重问题。