| Bill Hollinshead [MS] 2002-10-05, 8:52 pm |
| Dropping indexes in response to a corruption error message, if done often
enough (let's hope you don't <g> ), will inevitably be very likely to cause
irreparable data loss. When an index isn't corrupted, the index can be used
to work around page chain corruption (i.e., you can use a forced index to
select 'around' the corruption), for example. Thus, it is usually better to
first determine whether the corruption is within an index or within the
leaf level (before dropping anything). With version 6.5, this was a common
method to extract data. However, since 7.0, the proper (and usually very
easy) method (to fix a database) is to follow the instructions at the
bottom of the CHECKDB report. If there are no instructions (for example,
because CHECKDB stopped when raising the 8930 on sysindexes), or if
following those instructions does not fix the problem (i.e.,. the identical
error is raised again), then your best bet is to open an case with product
support.
However, while corruption in sysindexes indicates that the database is
almost ready to be dropped, there is at least one procedure that you can
attempt: Script out the database, create a new database, run the script on
the new database, use DTS (or BCP) to transfer the data out of the corrupt
database and into the new database, run sp_renamedb on both databases (so
that the new database is renamed to the corrupt database's name), and (when
satisfied that no more data can be extracted) finally drop the corrupt
database. If this doesn't work then please do open that case - no
guarantees <g>. Do you have backups that you know can be successfully
restored? If not, that support call will likely go quicker if you first
make file-level copies (SQL server must be stopped or the sp_dboption
'offline' might be useful) of the database files for master, model, msdb,
and any corrupt user database (before calling support).
However, following the above does not address why the database has an 8930
in the first place. Be sure to check the system event log for any errors or
warnings that contain "SCSI", "controller", "device", "disk", "driver",
"chkdsk", "unexpected", amongst other critical keywords. Also check the
application event log for chkdsk reports or other 'strangeness'. And ensure
the database files are not compressed. Once such hardware errors are
resolved, you should no longer see the 8930 raised once again. Until this
is investigated (including the temporary place of the databases upon a
different model box), the potential to see further corruption is quite high
<g>. SQL Server may also raise hardware problems via it's 823 error (so be
sure to inspect the SQL Server errorlogs). And of course, ensure that
Windows 2000 is running upon the latest drivers and firmware for that box's
hardware.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|