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

Introduction

Ensure that data purity checks are enabled on SQL Server databases to maintain column-value integrity.

Description

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:

-- Running DBCC CHECKDB with DATA_PURITY for a legacy database
DBCC 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.

How to fix

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:

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

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

Scope

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

Parameters

Rule has no parameters.

Remarks

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

Effort To Fix

13 minutes per issue.

Additional Information

There is no additional info for this rule.

Analysis Rules