How To Find and Fix SQL Server Integrity Violations: A Basic Troubleshooting Guide.

/ /

Part 1

Periodic integrity checks are fundamental in preventing data inconsistency in SQL Server. This article presents a basic approach using DBCC CHECKDB to detect and fix these inconsistencies when they occur. I will also show how to use it for proactive monitoring, which can help ensure that the DBMS is running properly. The techniques outlined here should be fairly easy to understand and apply, even by novice DBAs or developers who don’t have extensive experience with this level of troubleshooting.

The goal of any Database Management System (DBMS) is to provide an environment where applications can reliably store and retrieve application data in a secure yet transparent manner. This includes protecting the data from becoming corrupted by hardware or software errors. However, the DBMS itself is subject to these same risks and it needs to be protected from data corruption that can result from transactions that violate its integrity rules (e.g., trying to update a table’s clustered index by adding records without first deleting all previous ones).

The main protection mechanism implemented by SQL Server for this purpose is an automated system of “integrity checks” built into the Database Engine.

These integrity checks are performed as part of every DDL statement execution and consist mostly of automatically verifying:

  • That there exists no foreign key violation between two related tables.
  • Those dependent objects such as views or stored procedures exist and contain references to other objects in the same database.
  • Those dependent objects are not encrypted.
  • That the DBMS is prepared for creating SQL Server Agent jobs.

These checks are performed by the system stored procedure sp_checkautoprevoke, which validates all user-defined objects in the database against the current state of system metadata. The integrity check process can be initiated manually at any time through DBCC CHECKDB or DBCC validate_schema_update_against_catalog. Another option that serves as an alternative to automatically logging every single operation is using SQL Server Audit. It provides a declarative method to define what types of operations need to be audited, without complex event filtering mechanisms typically needed when using trace events.

This article covers the following approach to detect and fix SQL Server integrity violations:

  • Detecting when a DDL statement fails or runs into errors, which can indicate that there is an issue with dependent objects (views/stored procedures/triggers).
  • Querying the system catalog views in order to get information about object dependencies in a given database.
  • Identifying and fixing integrity problems in the current database through DBCC CHECKDB.

Detecting Dependent Object Integrity Violations during DDL Execution

When using DDL statements for creating new objects, it’s important that all objects exist and are valid before proceeding with their creation. In some cases, the process can be aborted with an error message indicating that there are objects in the database that don’t comply with integrity rules. After fixing related issues, it’s necessary to rerun DDLs again in order to create any new objects or perform other changes.

While using SSMS to edit SQL Server scripts where you intend to run DDLs against multiple databases at once, it might be useful to execute each script separately instead of posting/executing them all at once. This is because when you open a script file through SSMS, the Database Engine compiles it into an execution plan for every object in the script (if not done already) before populating its grid view. You should only use this approach if you’re 100% sure that the script is completely error-free. Otherwise, you might run into errors when executing some of its object definitions, which can lead to issues with dependent objects being created first.

The following example shows an execution plan for a stored procedure that opens another connection to the database on each execution of EXECUTE statement in order to test whether foreign key constraints are enforced or not on the dependency tables:

If this stored procedure was used as part of a larger script with other DDL operations performed against multiple databases, it would create more overhead than necessary by opening up new connections every time it’s executed. Therefore, instead of posting/executing all these scripts at once within the SSMS grid view, you should only do so selectively after fixing any potential errors that might be caught during their compilation. This will help you avoid wasting time with additional executions of the rest of the script, which would be completely unnecessary in this case.

If there are any errors raised when executing DDLs against a database, it’s best to check whether these problems can be fixed prior to running them again. If all dependent objects exist and are valid, then rerunning DDLs should result in success or no actions performed at all (for example, DROP). The following T-SQL query is an example of how you can retrieve information about objects that might fail DDL execution.

Conclusion:

SQL Server Audit provides an effective way to detect integrity violations in databases, which can indicate that there are issues with object dependencies. Querying system catalog views is another approach that can help you identify and fix these problems.