Thursday, November 3, 2011

Myths surrounding SQL database corruption and repair

A majority of users would usually go with the same traditional myth that a simple restart of SQL Server will be able to get away with most of their database corruption problems. Some people even attempt to reboot the Windows Server or attach/detach the SQL database. But, the bottom line is none of these methods can help you to resolve a case of database corruption. To deal with corruption, you can try to either restore the database using a recent good backup or repair it using a third-party SQL database repair tool.

When the database is in the 'SUSPECT or RECOVERY_PENDING' state, you cannot reattach the database as it requires crash recovery that is certainly impossible. So, you should not try to detach a corrupt database.

In the following cases, the rebooting option is very likely to work and fix most of the corruption:

  • If you just got a corrupt page image in the memory, however the same image on the hard disk seems to be consistent, you can try rebooting to resolve the issue.  
  • If you found that the corruption is real, but you performed an action as a part of the 'Reboot' process that made the page no longer to be allocated, the corruption issue may get resolved by simple rebooting.   
  • If you encounter a persistent stale read issue as the operation could not be completed in the I/O subsystem, you can reboot the system to quickly recover the broken subsystem.

There are many other things that are blindly followed as facts regarding SQL corruption and repair. These assumptions usually revolve around what a database repair can do, corruption causes, hidden corruption, etc.

Some people adamantly believe that repair should be run as default. But this does not yields expected results every time. Initially you need to address the underlying issue and then decide to go for a repair. If there is a damaged clustered index, the repair option will work. However, if you are running out of disk space, you certainly do not need a database repair. You do not always require taking your database offline as the problem may relate to some other issue.

No comments:

Post a Comment