In my last blog, I discussed how to check a database in MS-SQL, using DBCC checkdb. So, what happens if you check the database, and find a corrupt table. We will look at that today.
Looking at the output from the checkdb command, will give you an idea of what needs to be done to repair a table. The message will contain the minimum level of repair that is required. These will be something like REPAIR_ALLOW_DATA_LOSS, or REPAIR_REBUILD. In my experience, it is almost always REPAIR_ALLOW_DATA_LOSS. The first thing, of course, is to make a backup of your database.
Now, as to how to repair the table. First, the database will have to be in single user mode. Please note, that if you are using Counterpoint SQL, stop the Counterpoint services first, or they will almost instantly re-attach, and there goes your one use connection. Once the database is backed up, and in single user mode, you can proceed to repair the table.
The basic command to do the repair, is:
- DBCC checktable(‘TABLE_NAME’, REPAIR_ALLOW_DATA_LOSS)
This starts the repair routine, and can take quite a while on a large table. Once the repair does end, it will report as to what was, or was not, repaired. It has been my experience that it is very rare that repairs can not be made, and those cases have been where there was physical problems with the hard drive.
Now, assuming that checktable reported that it was able to repair the table, check for any missing data. It has been my experience that the actual loss of data is rare, but since the option used makes it possible, it needs to be checked. How this is done varies. It may be possible to check record counts or balances, depending on just what table was involved, and what information you have that can be verified.
Once the data has been checked for data loss, put the database back into multi-user mode. At that point, you are back up and running.