Noisy DBCC Rule in the SQL Management Pack

Article
10/01/2009

The SQL Server Management Pack has a rule, targeted at SQL 2000/2005/2008 DB Engine named “DBCC executed found and repaired errors”. This rule monitors for Event ID 8957 in the Application Log, logged by the SQL Service. Here is the criteria for the rule:

The problem with this is that you will see this Event logged, even when DBCC did not find or fix any errors…here’s an example:

So, this could result in a lot of alerts. Also, this rule generates an Informational alert by default, so if the event did indicate that errors were found, it would not generate a Warning or Critical alert.

We can change this by disabling the default rule and creating a new one. The new one should generate a Warning or Critical alert only if the DBCC event indicates that errors were found. We have two ways that we can do this:

The Easy Way

SQL 2005/2008:

Set an Override to disable the built-in “DBCC executed found and repaired errors” rules targeted at SQL 2005/2008 DB Engine
Create two separate rules, one targeted at SQL 2005 DB Engine and one targeted at SQL 2008 DB Engine. The rule will be exactly the same as the original, but with additional criteria for “Event Description does not contain “found 0 errors””:

Configure alerting as desired:

SQL 2000:

Set an Override to disable the built-in “DBCC executed found and repaired errors” rules targeted at SQL 2000 DB Engine
Create a new rule, targeted at SQL 2000 DB Engine. The criteria will be a little different than SQL 2005/2008:

The Harder, But Better, Way

I don’t like using Event Description in criteria if I don’t have to…using Parameters is less of a performance hit on the agent. So, instead of using the above options with EventDescription, we can use parameters. The parameters we are concerned with here are Parameter 8 (number of errors found) and Parameter 9 (number of errors repaired). This is for SQL 2005/2008 only, I haven’t looked at the parameters for SQL 2000. So, we can use the following criteria:

The reason that this is better is that the Agent doesn’t need to look through the full Event description of every 8957 event, it only needs to look at parameters 8 and 9.

The reason that is is harder is because we’ll also have to edit the XML of the rule, since we are using Integers in the criteria. See my previous blog post (from, like, an hour ago) on this here.

So, we’ll need to export our custom MP, open it in a text editor and find the XML for the OR expression for Parameter 8 and Parameter 9. Here is the XML:

<Expression>
   <Or>
     <Expression>
       <SimpleExpression>
         <ValueExpression>
           <XPathQuery Type="String">Params/Param[8]</XPathQuery>
         </ValueExpression>
         <Operator>Greater</Operator>
         <ValueExpression>
           <Value Type="String">0</Value>
         </ValueExpression>
       </SimpleExpression>
     </Expression>
     <Expression>
       <SimpleExpression>
         <ValueExpression>
           <XPathQuery Type="String">Params/Param[9]</XPathQuery>
         </ValueExpression>
         <Operator>Greater</Operator>
         <ValueExpression>
           <Value Type="String">0</Value>
         </ValueExpression>
       </SimpleExpression>
     </Expression>
   </Or>
</Expression>

We’ll then need to change the “String” entries to “Integer”.

Before:

<Expression>
   <Or>
     <Expression>
       <SimpleExpression>
         <ValueExpression>
           <XPathQuery Type= "String" >Params/Param[8]</XPathQuery>
         </ValueExpression>
         <Operator>Greater</Operator>
         <ValueExpression>
           <Value Type= "String" >0</Value>
         </ValueExpression>
       </SimpleExpression>
     </Expression>
     <Expression>
       <SimpleExpression>
         <ValueExpression>
           <XPathQuery Type= "String" >Params/Param[9]</XPathQuery>
         </ValueExpression>
         <Operator>Greater</Operator>
         <ValueExpression>
           <Value Type= "String" >0</Value>
         </ValueExpression>
       </SimpleExpression>
     </Expression>
   </Or>
</Expression>

After:

<Expression>
   <Or>
     <Expression>
       <SimpleExpression>
         <ValueExpression>
           <XPathQuery Type= "Integer" >Params/Param[8]</XPathQuery>
         </ValueExpression>
         <Operator>Greater</Operator>
         <ValueExpression>
           <Value Type= "Integer" >0</Value>
         </ValueExpression>
       </SimpleExpression>
     </Expression>
     <Expression>
       <SimpleExpression>
         <ValueExpression>
           <XPathQuery Type= "Integer" >Params/Param[9]</XPathQuery>
         </ValueExpression>
         <Operator>Greater</Operator>
         <ValueExpression>
           <Value Type= "Integer" >0</Value>
         </ValueExpression>
       </SimpleExpression>
     </Expression>
   </Or>
</Expression>

Update the version of the MP, re-import it, and you should be good to go.

Share via

Noisy DBCC Rule in the SQL Management Pack

Additional resources