KBA-01479: Best Practices: Using the Integrity Check Report


How and when do I use the Integrity Check Report?


The Integrity Check is in the Admin folder of the Report Browser, but it is more than a standard report.  Most reports only provide summary information in a pretty format; the Integrity Check report does three things:

  • Reads nearly everything in the system: document, rule, compliance issues, etc.
  • Checks for known data consistency issues (see KBA-01466 for a list).
  • Repairs certain data issues for which the automatic repair is deterministic and safe.

Many sites run this report only at the specific direction of Spitfire Support.  Others sites run it weekly; see the SQL JOBS tab in ICTool.

When possible, run the report during periods of low system usage—reading everything takes a lot of server resources.

Automatic Repairs 

The Integrity Report can “repair” certain issues, such as

  • Documents with a blank title can be given the default title
  • Changes to Commitment line account codings can be propagated back to payment requests and CCOs

Other issues can only be addressed sometimes:

  • Duplicate items numbers can be corrected sometimes, by either renumbering one of the items or deleting one.  But sometimes the duplicate item cannot be determined to a satisfactory degree and the duplicate items are left for manual review.

There are issues that are too gray for an automatic repair:

  • Compliance Issues may be found that do not match the compliance type configuration, but there is no way to know which is “right” and which is “wrong.”  If the tool deletes the compliance items and the configuration was inadvertently wrong, data loss would occur.

Those issues that are not repaired by the tool can be discussed with your implementer or Spitfire Support.

Additional Comments:

This report consumes significant server resources and should be run during periods of low system usage.  As a report, the tool is assigned a very low priority and will often be canceled if the SQL server cannot dedicate sufficient resources to the request.

The message in the report view may include ‘Query execution failed for dataset ‘sfDocSys‘ and suggest checking the report server logs. This is not necessary.

If immediate gratification is required, the Integrity Check tool can be run from SQL Management Studio; include a 1 to commit corrections:

    EXEC dbo.rq_IntegCheck 1;

In SQL Management Studio the tool runs with higher priority and permissions.  This does not guarantee adequate resources if the server is busy, but the actual cause of failure will be identified.

ICTool can be used to create a SQL Agent job that runs weekly on Sundays.

KBA-01479; Last updated: April 30, 2019 at 13:43 pm;
Keywords:  integrity