If data corruption occurs

If data corruption occurs

Below follows a few tips of what to do if, for instance, DBCC reports some sort of data corruption problem.

The tips should not be processed in any particular order. The action plan is always depending on the error etc. Do not perform the action unless you are confident with that action.

Note that it is always a good idea to do hardware diagnostics if you have corruption problems.

Record the error number, severity level and the message string

This is found in Db Maints’ various logs.

Search for the error number in Books Online

It is very easy to search in SQL Server Books Online for a particular error. Just enter the error number in the query window.

The recommendations from SQL Server Books Online (and/or KB) are generally the best recommendations to follow.

Search for the error number on Microsoft’s Knowledge Base (KB)

This is found on Microsoft’s Web and on Microsoft’s TechNet CD.

Do a log backup and then restore

If you have log backup in place, you can try to perform a log backup using the “NO_TRUNCATE” option. It is likely that the corruption will not re-surface when replaying the actions in the log (restoring that log backup). If you are able to do the log backup using the NO_TRUNCATE option, you will likely not lose one single committed transaction.

After performing this log backup (if possible), you restore the latest database backup, possibly the latest differential backup, and all subsequent log backups. If possible, do this restore to a new database name and verify this before switching over production to this database. You can rename a database using the “sp_renamedb” stored procedure.

Contact your support provider

It is likely that they will have a good recommendation for you. Even if the call will cost you money, it is probably inexpensive compared to loss of data.

Ask for tips on the Newsgroups

msnews.microsoft.com is Microsoft’s news server and it has a number of SQL Server related discussions areas. Remember to always be as specific as possible regarding your problem when asking for advice.

Note: All advice is not good advice. Remember that you (probably) do not know the person who advises you. That person is probably not familiar with your installation, or might quite simply give you bad advice. Always verify advice you get from the newsgroup.

Drop the index

If the problem only occurs in an index, you might get rid of it by simply dropping the index (and re-creating it).

Try to "get the data out"

If you have, for instance, a linkage problem in a table, you might be able to save the data in the table. You can try to use INSERT with a sub select, SELECT INTO, BCP or even Object Transfer (Transfer Manager) to copy the data to another table/database.

Turn off the "Suspect Flag", or turn on "Emergency Mode"

This is useful if the database is inaccessible, and you want to get to the data so you can use BCP or Transfer Management Interface to copy out the data.

This is not a way to repair the database. By turning of the suspect flag, you will only get rid of the symptom, not the cause. SQL Server (probably) turned on the suspect flag for some good reason.