DBCC CHECKDB shows errors

Results show 0 allocation errors and 42 consistency errors that all seem to be from one table. That is to the extent I understand the results. The errors are 8929, 8939, 8965, 8964 all for the same object id and varying text ids. The repair_fast and repair_rebuild did not work. Its calling for the repair_with_data_loss. Are there any work arounds? Does this mean that the table is corrupt? Is this a torn data page due to this table? If I need to run the repair_with_data_loss is there an easy way to determine which data has been lost? Welcome any feedback.Here are the general recommendations for handling a suspect or corrupt database:
0. 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.
1. If you can run DBCC CHECKDB against the database: Search Books Online and KB for the error
numbers that CHECKDB gives you. There might be specific info for that type of error.
2. 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, the 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.
3. If there is a hardware problem, ensure the faulty hardware is replaced.
4. Backup the log. This assumes that log backup schedule is in place, of course. If the database is
suspect, then the NO_TRUNCATE option for the RESTORE command must be used. Also, you might want to
do a file backup of the mdf and ldf files, for extra safety.
5. Restore is the best thing to do now. If you managed to backup log as per step 4, then you will
most probably have zero dataloss. You should restore the latest clean database backup and the
subsequent log backups including the one taken in above step.
If the database isn't suspect, then DBCC 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.
If the database is suspect, 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. I think
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 hand-hold you through the steps
appropriate for your particular situation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Bush" <anonymous@.discussions.microsoft.com> wrote in message
news:77FBEDC8-9B02-4CAB-970E-A96DC0F47EF9@.microsoft.com...
> Results show 0 allocation errors and 42 consistency errors that all seem to be from one table.
That is to the extent I understand the results. The errors are 8929, 8939, 8965, 8964 all for the
same object id and varying text ids. The repair_fast and repair_rebuild did not work. Its calling
for the repair_with_data_loss. Are there any work arounds? Does this mean that the table is
corrupt? Is this a torn data page due to this table? If I need to run the repair_with_data_loss is
there an easy way to determine which data has been lost? Welcome any feedback.|||Further to Tibor's comments, yes, you have corruption in your table. There
are no workarounds except restoring from a backup or running
repair_allow_data_loss (which will have to delete some data to fix this
particular corruption). There is no supported way of determining what data
has been lost apart from analyzing the error messages from DBCC.
I recommend you open a case with PSS who can help you address the
corruptions and also help you determine what caused them.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bush" <anonymous@.discussions.microsoft.com> wrote in message
news:77FBEDC8-9B02-4CAB-970E-A96DC0F47EF9@.microsoft.com...
> Results show 0 allocation errors and 42 consistency errors that all seem
to be from one table. That is to the extent I understand the results. The
errors are 8929, 8939, 8965, 8964 all for the same object id and varying
text ids. The repair_fast and repair_rebuild did not work. Its calling for
the repair_with_data_loss. Are there any work arounds? Does this mean
that the table is corrupt? Is this a torn data page due to this table? If
I need to run the repair_with_data_loss is there an easy way to determine
which data has been lost? Welcome any feedback.