我可以从 .NET 运行 DBCC CHECKDB 吗？

发布于 2024-07-22 05:38:41 字数 366 浏览 4 评论 0原文

我正在编写一个计划作业来模拟 SQL Express 的 SQL Server 维护计划。（我必须这样做，因为 SQL Express 不存在 SQL 代理和相关工具）

步骤之一是进行数据库完整性检查。其 TSQL 是：

DBCC CHECKDB(N'Northwind')  WITH NO_INFOMSGS

我如何知道在执行此命令期间是否发生错误，在使用 ADO.NET 时是否会抛出异常，或者我是否必须解析命令的文本输出（如果是的话，我在输出中查找什么）

这很难测试，因为我手头没有损坏的数据库。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

月竹挽风 2024-07-29 05:38:41

您可以将 TABLERESULTS 选项与 CHECKDB 结合使用 (DBCC CHECKDB WITH TABLERESULTS)。
这将为您提供一个记录集，其中包含 Error、Level、State、MessageText（以及许多其他列）等列。

该记录集的 Level 列（严重级别）应该足以确定是否存在任何错误。

MS 表示，11 至 16 级“由用户生成，并且可以由用户纠正”。因此，我认为高于 17 的任何内容都应该意味着：停止进行任何备份（以避免用损坏的备份覆盖良好的备份），如果可能，使系统脱机，并立即通知操作员。

11 到 16 级可能也应该报告给操作员（通过常规电子邮件或其他方式），以便他可以在必要时进行检查。我不确定 CHECKDB 是否会报告级别 11 到 16 的错误。不过，在其中记录错误/通知操作员的代码可能不会有什么坏处。）

（不过， >注意：如果将 TABLERESULTS 与 NO_INFOMSGS 结合使用，并且 CHECKDB 没有发现任何错误，您将没有得到任何记录集作为结果，甚至没有一个没有行的记录集。

注意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.

我不太使用 ADO.NET，但我认为 ADO.NET 会通过抛出异常来做出反应。
另外，由于这是一个严重性 >= 20 的错误，因此会导致客户端连接关闭。

总而言之：我将运行DBCC CHECKDB WITH TABLERESULTS。如果命令失败，则存在问题（可能是严重问题）。如果没有，请继续循环遍历结果集，并查找任何严重性级别 >= 17。如果找到，则可能也存在某种严重问题。

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.

回复收藏 0 原文