How to Find & Resolve Data Corruption in SQL Server Database

Data corruption occurs when database simply dies in the middle of processing data. Process can be stop in the middle due to power failure and sql server shutting down in the middle of processing data. In this situation, some other hardware malfunction, database or indexes get corrupted. Database can no longer be used by SQL Server, until it completely repaired. Fortunately, there are numerous steps to protect data in the event of data corruption. First and foremost that everyone suggested for applying a good backup strategy. However, I'll explain few other techniques, based around the various DBCC commands, and a script that will make sure corruption issues are discovered.

Seeking Out Corruption:

To set up regular integrity checks using Maintenance Plan is the best way to find out the corruption on SQL database. DBCC CHECKDB is the main command which is used to test and fix consistency errors in SQL Server databases. DBCC CHECKDB is actually one command which is combination of DBCC commands, DBCC CHECKCATALOG, DBCC CHECKALLOC and DBCCCHECKTABLE So there is no need to run these command separately DBCC CHECKDB includes these other commands so negates the need to run them separately.

Recommended Solutions:

After running this command, it shows an error message to the SQL Server ERRORLOG. The most of the messages show how many database consistency errors were found and how many were repaired. Here are some general approaches if errors are reported.

• The first & foremost solution if DBCC CHECKDB reports any consistency errors is to restore from a healthy backup copy.
• However, if you are unable to restore from a backup copy, then CHECKDB command provides a feature to repair errors. It is not necessary that repair option will actually fix all the errors. Furthermore, not all the reported errors may require this level of repair to resolve the error.

Recommendations for Good DBCC Performance:

1. Run CHECKDB only when the PC usage is low.
2. At the same time when you are running CHECKDB, do not run other disk I/O operations, such as disk backups.
3. Place tempdb on a separate disk system or a fast disk subsystem.
Allow enough room for tempdb to expand on the drive.
4. At the same time when you are running CHECKDB, Avoid to run CPU-intensive queries or batch jobs.
5. Try to reduce active transactions while a DBCC command is running.
6. Use the NO_INFOMSGS option to reduce processing and tempdb usage significantly.

Summary: In this article, I have discussed in which scenarios database get corrupted and explain DBCC options that will assist you when corruption happens in your database. Good backup is the best approach as ever to recover corrupt database.