Why Regular Consistency Check is Important?

Consistency Check always plays a very important role in every database administrator’s life.The number of individuals does not like to check it on the daily basis.For a regular user, this is the way of continuous checking of database corruption, which can happen due to numerous reasons and no-one can predict it. It checks the logical and physical integrity of the database objects in the specified database. The best point is that it also recommends suitable repairing options depending upon the level of corruption for repairing the selected database. Now the million dollar question comes in very DBA mind; what is the best suitable time-interval to run this consistency check on the database.

When a user should run consistency check:Well it depends on the situation. Here I have explained two examples.

• Suppose you have an unreliable I/O subsystem and your database is running on the same machine then it is indicating that your database is on high risk. A database might be corrupt or damaged anytime. Running consistency check at the earlier stage helps you in finding the problem as it starts. A minor corruption can cause a big data loss from the database. 

• Suppose you have an accurate I/O subsystem, with up-to-date drivers and firmware. You have an inclusive backup strategy which you have tested and you have full confident that, it will recover from the data corruption with minimum downtime and data loss. In this case a user may be comfortable with consistency check once in a week. 

The important factor is that, how many times user runs the consistency check and how much comfortable with I/O subsystems and capability to recover from corruption problem? Loss of integrity in your database today then, User probably runs DBCC CHECKDB daily.

Note:If a user does not aware about consistency check and its benefits; it can be digestive but, If the user is so much aware of it then, the consistency check is always expected. Even if you think that you cannot run it because it takes too much time/resources etc.

Detect corruption on other way:When a database corruption detected by the regular operation. It has been noticed that many databases do not use (read/update) their many parts on the daily basis. It means the unused parts of the database got corruption and user is not aware of it because user is not running the consistency check to know the problem. It means that users are not trying to know that I/O subsystem causing a problem. By this problem, the database can be corrupt and it affects the other part of it.

During improvement or migration: I have seen many times that people always use consistency check after migration but not before. After every update DBCC CHECKDB has been improved especially from 2000 to 2005. Suppose you run the consistency check on the migrated/upgraded database and got corruption then, you will perform following options like

• Run repair options: repair_fast, repair_rebuild, and repair_allow_data_loss
• Restore from the backups: it might be possible that your backup has been corrupt too. 

It has been noticed that, corruption always available on before but discovered after upgrades. So always run the consistency check before any upgrade.