Database Corruption is bad ! And if corruption get transported into the backups, then situation become worst. DBCC CHECKDB act as a security check to find out the corruption in SQL Server. Performing consistency check must be the regular activity of DBAs,to avoid any type of corruption in SQL Server Database.
But, it is estimated that only 27% of DBAs run daily basis of consistency check. Thats too sad! It should be performed on the regular basis. Many performs DBCC CHECKDB weekly, making crucial data on the risk, unaware of that performing weekly checks also lead to corruption as sometimes corruption occur between weekly DBCC CHECKDB and it does not alert you till the next backup is taken, making condition more worse.
Hence, DBCC CHECKDB is one of the the best way to perform consistency check on the database. But sadly most DBAs does not know when to run it and how often to run DBCC CHECKDB.
But first let us understand Why it is important to run DBCC CHECKDB.
Importance of Running DBCC CHECKDB Command
Running DBCC CHECKDB checks the physical and logical integrity of all the database objects by performing DBCC CHECKALLOC, DBCC CHECKTABLE, DBCC CHECKCATALOG. Running DBCC CHECKDB command with any Repair option are completely recoverable.
Running DBCC CHECKDB helps you detecting corruption in SQL Server database . It alerts you when any type of corruption persists in your database. Regular performing checks helps you in detecting corruption and prevent your crucial data from data loss.
If the corruption spread in your database, the damage can lead to the huge amount of loss of crucial data making SQL Database inaccessible.
I think this might clear the concept of how important is running DBCC CHECKDB command is and one can avoid these corruption related problem by running DBCC CHECKDB regularly to check database integrity.</p
How Often Should you run Database consistency Check in SQL Server ?
This is one of the most commonly asked query, that how frequently should one run DBCC CHECKDB? Well, it totally depends on the amount of data you afford to loose. The more you increase the gap between the last check and the next check, more is the risk of database corruption or data loss.
Many organization cannot afford to run this on the daily basis as the process is time consuming & resource intensive . For that case, you can check consistency, weekly or once in a two week.
Though its not always possible to run DBCC CHECKDB daily on the production server, but if you can’t afford to loose a single day data,then it is highly advised to run it everyday. Or if you afford to loose it, then you can choose to do it weekly or even monthly.
What If DBCC CHECKDB job failed?
Performing DBCC CHECKDB is a resource intensive task and somehow for many organization it is not possible to regularly run consistency check. This puts their data at a greater risk of corruption. So, if you failed to perform regular consistency check and you want to avoid the suffering of huge amount of data loss & protect your database from corruption, you must try SQL recovery tool.
Software works when the consistency check fails. It helps you in repairing your database from corrupt state. You have to simply add mdf file to the software and your file will get recovered successfully.
Conclusion
Performing consistency check is the alert key to protect your SQL Server database from any kind of protection. We have discussed how often should you perform consistency check to protect your database. And what happen if you fail running it ? The blog covers its solution, if you fail to run DBCC CHECKDB or if you are having corrupted database file.
IgnacioSalom.com 