In this article, we look at factors which a DBA considers before deciding the required frequency of running DBCC CHECKDB.
While running SQL Server, corruption of database is the biggest fear of the DBA. And if that corruption is transported to the backups, it becomes even more problematic. To avoid corruption of databases from expanding, and becoming a part of your backups, it is important to regularly apply security checks to your databases. DBCC CHECKDB is one of the most preferred security check available in SQL Server, for running on your databases, it is a resource intensive practice, that is why the regularity of these checks differs with different organizations. This is one of the best ways to check for corruption in SQL Server databases.
Why is it Important to Run DBCC CHECKDB
You might or might not be a DBA, but if you have access to the databases, and are responsible for the safe keeping of user databases, it is a must for you to run DBCC CHECKDB. It not only helps you spot corruption by alerting you, whenever it spots corruption, but also allows you to prevent it from spreading. If the corruption spreads, the damage can become unmaintainable, leading to large amounts of data loss. Corrupted databases will not only send out wrong information, but might also become totally unable to respond to the queries. All of this can be avoided if you opt for regularly using DBCC CHECKDB.
How Often Should You Make Use of DBCC CHECKDB
One of the most commonly asked question, related to the feature is, how frequently should we run DBCC CHECKDB? This process is time consuming and resource intensive, many organizations cannot afford to run it daily. It is advisable to run these checks weekly or at max, once every two weeks. Even if you choose to run it once a week, corruption caused in the database after the last backup, will not be recovered. Your database might get corrupted within a week’s time between the last check and the next check, thus making you lose a week of your progress.
The more you keep on increasing the gap between the last check and the next check, the greater is the risk of corruption, and data loss. Therefore, how frequently you want to use this feature should depend on how much data can you afford to lose? If you cannot lose even a day’s progress, you will have to do it daily. If you can afford to lose some data, you might choose to do it weekly or even monthly.
Key Point to Keep in Mind
DBCC CHECKDB is certainly a very helpful tool for spotting corruption, but since it consumes resources, many organizations do not use it very regularly. Although this puts their data at a greater risk of corruption, most of the organizations run DBCC CHECKDB only once a week. This is probably the most convenient time gap for a lot of organizations, it is neither too long, to cause great loss of data, nor too short to consume lots of resources. Also, to completely avoid data loss, organizations must invest in an mdf fix tool.
Author Introduction:
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover access and sql recovery software products. For more information visit www.datanumen.com
Leave a Reply