Home
About me and Nucleus
Partners
Links
Articles, tips etc.
My blog
Microsoft articles
Newsgroups
Course demo files
Sync logins
SMTP Alerter
Encountering a suspect database or corruption in a database is a rare thing. It can happen, however, most often due to faulty hardware or operational mistakes (like deleting a transaction log file).
More information
The points below are recommendations for handling a situation where you have some type of corruption in a database or if the database goes suspect. This article has been compiled with help of a number of SQL Server MVP's as well as members of the SQL Server developer team.
You are welcome to share this information, in any shape or form.
Details
- Ensure you have a backup strategy that you can use to recover from
hardware failures (including corruption). I recommend performing both database
and log backup in most situations.
- If you can, run DBCC CHECKDB against the database: Search Books Online and
KB for the any error numbers returned. There might be specific recommendations
for your error messages.
The NO_INFOMSGS option of DBCC is helpful, it makes DBCC return error messages only.
- Find out why this happened. Check eventlog, do HW diagnostics etc.; search
Books Online and KB for those errors. You don't want this to happen again! If
the database is suspect, a database file might have been in use by for instance an
anti-virus program and restarting SQL Server might be all that is needed - but
you still want to read logs etc. to find out what happened.
- If there is a hardware problem, ensure the faulty hardware is
replaced.
- Backup the log. This assumes that log backup schedule is in place. If the database is suspect, you must use the NO_TRUNCATE option for the
BACKUP command. Also, you might want to do a file backup of the
mdf and ldf files, for extra safety.
- [a] Restore is the best thing to do now. If you managed to backup log in above step, then you will most probably have zero dataloss. Then restore
the latest clean database backup and the subsequent log backups, including the
one taken in above step.
As of SQL Server 2005, we have page level restore, meaning that we can restore only the damaged pages (instead of a full backup) and then the subsequent log backups.
[b] If the database isn't suspect, then DBCC CHECKDB with a REPAIR option might be a secondary option but this will often result in loss of data. Additional solutions, depending on the errors, may be to manually rebuild non-clustered indexes, manually drop and reload a table if the data is static, and so on.
[c] If the database is suspect and 2000 or earlier, a secondary option can be to try to "un-suspect" the database using sp_resetstatus. Read about it (books online, KB, google etc). It might help but if the database is too damaged, it might just pop back to suspect again. There's also something called "emergency mode" which is a "panic" status you can set in order to try to get data out of a damaged database. The name of that option speaks for itself. Again search the net for info.
If you feel uncertain with above steps, I recommend letting MS Support hand-hold you through the steps appropriate for your particular situation.