SA0083 : Consider proactively checking the logical and physical integrity of all the objects in the database

Regularly use DBCC CHECKDB to identify potential database corruption and ensure data integrity.

Description

Regularly running DBCC CHECKDB is crucial for maintaining the integrity of your databases in SQL Server. This command checks for consistency errors and potential corruption in database tables and indexes. Without regular checks, databases might harbor undetected errors, leading to severe data issues.

For example:

SQL
1-- Query that should execute regularly
2DBCC CHECKDB('YourDatabase');

If this command is not run frequently, unnoticed corruption may remain, potentially leading to these problems:

  • Data loss: Corrupted data files can lead to loss of critical information.

  • System downtime: Unchecked corruption might result in unexpected system outages or maintenance periods.

How to fix

Ensure database integrity and identify potential database corruption by regularly using the DBCC CHECKDB command.

Follow these steps to maintain the integrity of your databases:

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

  2. Navigate to the “Databases” node in Object Explorer and identify the database you want to check.

  3. Use the following DBCC CHECKDB command to check for consistency errors and potential corruption:

For example, execute the following query to check the integrity of the database named ‘YourDatabase’:

SQL
1-- Execute this command to check the database
2DBCC CHECKDB('YourDatabase');

  1. Review the results for any errors or warnings. Address any issues as needed, following the guidance in the SQL Server documentation or consult with a database expert if severe corruption is detected.

  2. Schedule regular execution of DBCC CHECKDB to ensure ongoing database integrity and avoid potential data loss or system downtime.

Scope

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

Parameters
Name Description Default Value
CheckPeriodInDays

Maximum number of days for running database integity check.

7

Remarks

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

Effort To Fix
13 minutes per issue.
Categories

Maintenance Rules, Bugs

Additional Information

There is no additional info for this rule.

See Also

Other Resources