Who is this article for?

IT Administrators investigating data-related issues.

Database access is required.

Sometimes it is necessary to check data integrity.

This article describes four basic things to do if a data-related issue is suspected.  They help build a picture that can then be used to target further diagnosis.

Note that this article describes steps to take around application data integrity, not the physical integrity of SQL Server databases.

1. Overview

If we suspect the cause of an error relates to data, then there are certain basic checks we need to do.  Once we have done these then we can plan next steps.

In summary, we:

1. Follow the steps shown here
2. Capture the results
3. Analyse the results
4. Consider next steps to troubleshoot / fix

2. Running checking tools

2.1. CheckDataIntegrity

To run the tool:

1. Access the database.
2. Run the stored procedure CheckDataIntegrity:

EXEC CheckDataIntegrity;

3. Save any results in a CSV file.
   This should be a single output.

Example of CheckDataIntegrity finding an issue

2.2. CheckSchemaIntegrity

To run the tool:

1. Access the database.
2. Run the stored procedure CheckSchemaIntegrity:

EXEC CheckSchemaIntegrity;

3. Save any results in a CSV file.
   This should be five outputs.

Example of CheckSchemaIntegrity finding no issues

3. Checking active business rules

To check business rules:

1. Access the database.
2. Run these three queries:

SELECT COUNT(*) FROM [Behaviors] WHERE ACTIVE = 1;
SELECT COUNT(*) FROM [Restrictions] WHERE ACTIVE = 1;
SELECT COUNT(*) FROM [Validations] WHERE ACTIVE = 1;

3. Make a note of the output.

4. Reviewing the InstallLog

To review the log:

1. Access the database.
2. Run this query:

SELECT * FROM InstallLog;

3. Save the results in a CSV file.

Example InstallLog from the Ideagen demo database

5. Investigating the output

To help us investigate issues:

1. Run the above steps.
2. Save the output
3. Return the files to our Support team for further investigation.
4. If problems are found:
   • Where the cause is a known issue and the system is on-prem, the Level 1 team will often be able to provide information on corrective action.
   • Where the cause is known and the system is hosted, the Level 2 team will often be able to apply a solution.
   • If the cause is unknown then issues will be escalated to Level 3 or Development for further investigation.