This post is written by: Priyanka Chouhan
Every company or an individual, who use database systems, always wants to ensure the database integrity of the stored database. Therefore, you should always run the database consistency against your database, to know the accuracy and purity. In order to regularize the database maintenance routine, run DBCC CHECKDB daily, weekly, monthly. You might have thinking that how do you know what data potentially gets lost? Your next question could be about the performance enhancements after running the DBCC CHECKDB and also about the need of running DBCC CHECKDB without any performance implications.
At times, database system specifically mentions that using this option leaves the database in an unsupported configuration, but still if you proceed then there are chances of database inconsistency. This write-up lets you know the process of identifying the possible potential data loss when DBCC CHECKDB reports corruption in your SQL Server database.
Carefully read the output from DBCC CHECKDB: After running the DBCC CHECKDB, it will show the information in bulk to get you started. Read the output thoroughly, and remember the below mentioned information.
Identify the affected objects: You need to use the output of DBCC CHECKDB to identify the affected object. Usually DBCC CHECKDB does not recommend the REPAIR_ALLOW_DATA_LOSS option for every corruption case. When you identify errors it will give you an Object in ID value of the affected object, which can be used to find out the affected object and then you can get the infected table.
Identify the affected page: After knowing the information about the affected object, you can decide whether the infected data is critical or not. If you talk about an organization, then all the data can be considered as critical and need a treatment. For example, let’s assume that the identified data is critical and you need to minimize the data loss. By identifying the unprocessed page number from the output of DBCC CHECKDB, you can determine the corrupted page. Additionally, it will also explain you about the reference pages related to the corrupt page. Ultimately, it will provide you the complete information that is required to know about the corruption in the DBCC CHECKDB output.
Resolve the corruption: After successfully find out the corrupted page along with its index format. You do not need to restore from backup or run REPAIR_ALLOW_DATA_LOSS to repair; instead you can just disable and rebuild the index. The time taken is dependent upon the size of the index and the impact it has on the system. It's up to you to decide that either you will do it during or after working hours.
Since you have the latest backup and you are planning to restore your database from it, then drop this idea. It would be useless to restore data from the backup, as this restoring process will consume lots of time, in case you own a big database. Therefore, it is better to repair only known corrupted pages. Disabling and rebuilding the index is always a better way to make your complete database offline to resolve the corrupted non-clustered index. However, whenever the DBCC CHECKDB reported a bunch of errors, it is not always compulsory to take your database offline to fix it.
Every company or an individual, who use database systems, always wants to ensure the database integrity of the stored database. Therefore, you should always run the database consistency against your database, to know the accuracy and purity. In order to regularize the database maintenance routine, run DBCC CHECKDB daily, weekly, monthly. You might have thinking that how do you know what data potentially gets lost? Your next question could be about the performance enhancements after running the DBCC CHECKDB and also about the need of running DBCC CHECKDB without any performance implications.
At times, database system specifically mentions that using this option leaves the database in an unsupported configuration, but still if you proceed then there are chances of database inconsistency. This write-up lets you know the process of identifying the possible potential data loss when DBCC CHECKDB reports corruption in your SQL Server database.
Carefully read the output from DBCC CHECKDB: After running the DBCC CHECKDB, it will show the information in bulk to get you started. Read the output thoroughly, and remember the below mentioned information.
Identify the affected objects: You need to use the output of DBCC CHECKDB to identify the affected object. Usually DBCC CHECKDB does not recommend the REPAIR_ALLOW_DATA_LOSS option for every corruption case. When you identify errors it will give you an Object in ID value of the affected object, which can be used to find out the affected object and then you can get the infected table.
Identify the affected page: After knowing the information about the affected object, you can decide whether the infected data is critical or not. If you talk about an organization, then all the data can be considered as critical and need a treatment. For example, let’s assume that the identified data is critical and you need to minimize the data loss. By identifying the unprocessed page number from the output of DBCC CHECKDB, you can determine the corrupted page. Additionally, it will also explain you about the reference pages related to the corrupt page. Ultimately, it will provide you the complete information that is required to know about the corruption in the DBCC CHECKDB output.
Resolve the corruption: After successfully find out the corrupted page along with its index format. You do not need to restore from backup or run REPAIR_ALLOW_DATA_LOSS to repair; instead you can just disable and rebuild the index. The time taken is dependent upon the size of the index and the impact it has on the system. It's up to you to decide that either you will do it during or after working hours.
Since you have the latest backup and you are planning to restore your database from it, then drop this idea. It would be useless to restore data from the backup, as this restoring process will consume lots of time, in case you own a big database. Therefore, it is better to repair only known corrupted pages. Disabling and rebuilding the index is always a better way to make your complete database offline to resolve the corrupted non-clustered index. However, whenever the DBCC CHECKDB reported a bunch of errors, it is not always compulsory to take your database offline to fix it.
No comments:
Post a Comment