SA0084 : Data purity check is not enabled for the current database

In SQL Server, ensuring the integrity of data stored in database columns is crucial, especially when databases are upgraded from older SQL Server versions. Data purity checks verify that the data’s value is consistent with the column data type.

For example:

1-- Running DBCC CHECKDB with DATA_PURITY for a legacy database
2DBCC CHECKDB(N'YourDatabaseName') WITH DATA_PURITY;

This command is necessary for databases originally created in versions prior to SQL Server 2005, as they do not have data purity checks enabled by default post-upgrade. Running this command ensures that future integrity checks will include column-value verification.

• Failure to enable data purity checks can result in unnoticed data corruption.

• Ensuring column-value integrity helps maintain reliable data operations and outcomes.

Enable data purity checks to ensure column-value integrity in SQL Server databases, especially for databases upgraded from earlier versions.

Follow these steps to address the issue:

1. Open SQL Server Management Studio (SSMS) and connect to the target database instance.

2. Identify any databases that have been upgraded from SQL Server versions prior to 2005 as they may not have data purity checks enabled.

3. Run the DBCC CHECKDB WITH DATA_PURITY command on each identified database to enable data purity checks. This can be executed by opening a new query window in SSMS and entering the command.

For example:

1-- Running DBCC CHECKDB with DATA_PURITY for a legacy database
2DBCC CHECKDB(N'YourDatabaseName') WITH DATA_PURITY;

Ensure you replace YourDatabaseName with the actual name of your database.

The rule has a ContextOnly scope and is applied only on current server and database schema.

Rule has no parameters.

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Maintenance Rules, Code Smells

There is no additional info for this rule.

Other Resources