After a hiccup on my server computer, Windows ran CHKDSK on the next
reboot.
Since then, DBCC CHECKDB now returns the following:
Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC that prevented further processing.
Contact Customer Support Services.
And DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) returns:
Msg 121, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the
server. (provider: TCP Provider, error: 0 - The semaphore timeout
period has expired.)
Based on what I could dig up, both of these messages may be caused by
hardware errors (even though a repeated CHKDSK /R finds no additional
problems).
I have tried copying the database file and running DBCC CHECKDB
against the copy (in case a flaky sector is causing the problem).
However, this returned the same error.
Given that a hardware error may be the original cause of this problem
and given that I would like to avoid restoring to backup, is there
anything I can do to resolve the problem (other than call MS PSS)?
For example, is there some way to force DBCC CHECKDB to continue past
the problem? Or, is there some way to backup all of the data that can
be accessed (through a single command or through programming)?
Any help is appreciated!
Gary Geniesse
NeuroDimension, Inc.
> Based on what I could dig up, both of these messages may be caused by
> hardware errors (even though a repeated CHKDSK /R finds no additional
> problems).
CHKDSK can detect/correct only file system problems. It appears you have
corruption within database files (e.g. torn pages) due to the previous
hardware problem.
> Given that a hardware error may be the original cause of this problem
> and given that I would like to avoid restoring to backup, is there
> anything I can do to resolve the problem (other than call MS PSS)?
Your best course of action is to restore from backup and apply transaction
log backups. The REPAIR_ALLOW_DATA_LOSS of DBCC should be used only as a
last resort to salvage data and with the understanding a significant about
of data might be lost. I suggest you make copies of the database files
before taking further corrective action.
It looks like the on-disk data corruption that is too severe for normal DBCC
processing. There is no technique can materialize data that no longer
exists but, depending on the nature of the corruption, you might be able to
salvage data by copying data to another database with the same schema. I
suggest you don't use this database for any purpose other than salvage going
forward.
Hope this helps.
Dan Guzman
SQL Server MVP
"GaryGen" <gary@.garygen.com> wrote in message
news:1184966021.465560.312450@.w3g2000hsg.googlegro ups.com...
> After a hiccup on my server computer, Windows ran CHKDSK on the next
> reboot.
> Since then, DBCC CHECKDB now returns the following:
> --
> Msg 8967, Level 16, State 216, Line 1
> An internal error occurred in DBCC that prevented further processing.
> Contact Customer Support Services.
> --
> And DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) returns:
> --
> Msg 121, Level 20, State 0, Line 0
> A transport-level error has occurred when receiving results from the
> server. (provider: TCP Provider, error: 0 - The semaphore timeout
> period has expired.)
> --
> Based on what I could dig up, both of these messages may be caused by
> hardware errors (even though a repeated CHKDSK /R finds no additional
> problems).
> I have tried copying the database file and running DBCC CHECKDB
> against the copy (in case a flaky sector is causing the problem).
> However, this returned the same error.
> Given that a hardware error may be the original cause of this problem
> and given that I would like to avoid restoring to backup, is there
> anything I can do to resolve the problem (other than call MS PSS)?
> For example, is there some way to force DBCC CHECKDB to continue past
> the problem? Or, is there some way to backup all of the data that can
> be accessed (through a single command or through programming)?
> Any help is appreciated!
> Gary Geniesse
> NeuroDimension, Inc.
>
|||Thanks for the reply. I appreciate the help!
> Your best course of action is to restore from backup and apply transaction
> log backups. The REPAIR_ALLOW_DATA_LOSS of DBCC should be used only as a
> last resort to salvage data and with the understanding a significant about
> of data might be lost. I suggest you make copies of the database files
> before taking further corrective action.
Understood. Unfortunately, the first time you really need a backup is
when you discover your backup strategy is not adequate/not set up
properly. So yes, I was using REPAIR_ALLOW_DATA_LOSS with the
understanding that some data might be lost.
The odd thing is, I'm currently converting some software from ADODB to
ADO.NET. The ADO.NET routines keep exiting out due to "a logical
consistency-based I/O error: incorrect checksum", but the ADODB
routines seem to be running fine. Evidently ADODB or the old routines
are coded in such a way as to be not as sensitive to consistency
errors. Therefore, I'm not expecting excessive data loss, just a small
subset of relevant records to be hosed (if any).
> It looks like the on-disk data corruption that is too severe for normal DBCC
> processing. There is no technique can materialize data that no longer
> exists but, depending on the nature of the corruption, you might be able to
> salvage data by copying data to another database with the same schema. I
> suggest you don't use this database for any purpose other than salvage going
> forward.
Understood. If data is lost, it's lost. I'm not expecting DBCC to
recreate it. However, since a REPAIR_ALLOW_DATA_LOSS option was
available, it seemed reasonable that it could cleanse the database
enough to clear the error, even at the expense of mangling or deleting
the page with the bad checksum. I was definitely surprised that
CHECKDB returned messages like "internal error" and "transport error",
especially when the disk itself should no longer be returning errors.
It would seem like CHECKDB should be robust enough to handle this
case.
I'm currently trying Copy Database. If that fails, I'll try a more
granular approach. Thanks again for your help!
Gary Geniesse
NeuroDimension, Inc.
No comments:
Post a Comment