DBCC CHECKDB in SQL Server 2000 - inconsistent results

Veritas Backup Exec has recently started reporting occasional CHECKDB errors
in our primary SQL Server database. When I run CHECKDB manually, it sometimes
finds table inconsistencies and doesn't at other times; the inconsistencies
found are in different tables at different times, so there is no consistent
result. I have run DBCC CHECKDB(DatabaseName, REPAIR_ALLOW_DATA_LOSS) twice,
once immediately after CHECKDB without repair reported inconsistencies, but
no errors were found to repair either time. Has anyone else out there
encountered this sort of thing? Is there anything else I should be doing?
Thanks in advance...This inconsistency is sometimes seen when there is a drive problem.
One possible scenario is:
* At some point in time your drive failed to read/write and this caused the
data to be corrupt
* DBCC CHECKDB found inconsistencies
* You repaired these by running the REPAIR_ALLOW_DATA_LOSS
* however, if any drive (or drive controller) issues are still outstanding,
it is certainly possible for SQL database to become corrupt again.
1. You want to start by checking the SQL Error logs, are there any 823
errors in there? PRB: Error message 823 may indicate hardware problems or
system problems - ID: 828339. This might indicate that Microsoft SQL
Server 2000 has detected hardware or system problems when it was reading
from or writing to database files
2. Check the application and system event logs. Are there any drive or
drive controller errors? Are there any Delayed Write Failed...errors?
3. You can use the SQLIOSTRESS utility to perform stress tests on disk
subsystems to simulate Microsoft SQL Server 2000 and Microsoft SQL Server
7.0 read, write, checkpoint, backup, sort, and read ahead activities.
http://support.microsoft.com/?id=231619
4. If possible, engage your hardware vendor to fully test and certify the
system.
5. When was the last time you updated disk or controller drivers? Are
they up to date?
KB article references (available at http://suport.microsoft.com):
----
--
86903 INF: SQL Server and Caching Disk Controllers
234656 Using Disk Drive Caching with SQL Server
46091 INF: Using Hard Disk Controller Caching with SQL Server
826433 Additional SQL Server Diagnostics Added to Detect Unreported I/O
231619 Use the SQLIOStress Utility to Stress a Disk Subsystem Such as
SQLServer
332023 Slow Disk Performance When Write Caching Is Enabled
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx|||Thanks for your prompt and thoughtful reply. We have already run CHKDSK on
the hard drive involved and found no errors, and one of my associates has
checked error logs to no avail. We will proceed with the more thorough checks
you recommend.
In your proposed scenario, you hypothesize that REPAIR_ALLOW_DATA_LOSS fixed
the errors present, but would it do this without displaying any sort of
relevant message? Both times I ran REPAIR_ALLOW_DATA_LOSS, I specified WITH
ALL_ERRORMSGS, but each time the utility returned the message "CHECKDB found
0 allocation errors and 0 consistency errors in database xxxxx".
Thanks again... Steve B.
"Fany Vargas [MSFT]" wrote:
> This inconsistency is sometimes seen when there is a drive problem.
> One possible scenario is:
> * At some point in time your drive failed to read/write and this caused the
> data to be corrupt
> * DBCC CHECKDB found inconsistencies
> * You repaired these by running the REPAIR_ALLOW_DATA_LOSS
> * however, if any drive (or drive controller) issues are still outstanding,
> it is certainly possible for SQL database to become corrupt again.
> 1. You want to start by checking the SQL Error logs, are there any 823
> errors in there? PRB: Error message 823 may indicate hardware problems or
> system problems - ID: 828339. This might indicate that Microsoft SQL
> Server 2000 has detected hardware or system problems when it was reading
> from or writing to database files
> 2. Check the application and system event logs. Are there any drive or
> drive controller errors? Are there any Delayed Write Failed...errors?
> 3. You can use the SQLIOSTRESS utility to perform stress tests on disk
> subsystems to simulate Microsoft SQL Server 2000 and Microsoft SQL Server
> 7.0 read, write, checkpoint, backup, sort, and read ahead activities.
> http://support.microsoft.com/?id=231619
> 4. If possible, engage your hardware vendor to fully test and certify the
> system.
> 5. When was the last time you updated disk or controller drivers? Are
> they up to date?
> KB article references (available at http://suport.microsoft.com):
> ----
> --
> 86903 INF: SQL Server and Caching Disk Controllers
> 234656 Using Disk Drive Caching with SQL Server
> 46091 INF: Using Hard Disk Controller Caching with SQL Server
> 826433 Additional SQL Server Diagnostics Added to Detect Unreported I/O
> 231619 Use the SQLIOStress Utility to Stress a Disk Subsystem Such as
> SQLServer
> 332023 Slow Disk Performance When Write Caching Is Enabled
>
> Fany Vargas
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
> Microsoft software to better protect against viruses and security
> vulnerabilities. The easiest way to do this is to visit the following
> websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>|||If dbcc checkdb ( 'gfrc_004',REPAIR_ALLOW_DATA_LOSS)
WITH
ALL_ERRORMSGS
does not report any errors - it means no errors were found. If any errors
were fixed you should see a summary of what errors were fixed. Some
errors are not repairable, if you run DBCC CHECKDB (without any options)
you will see what the minimun repair level is.
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx