Since we have migrated our 2000 databases to SQL 2005 on new hardware (described below), we have experienced re-occurring corruption on the same tables and indexes even after the corruption had been resolved by dropping/re-creating the indexes manually. Typical events include:
1. Clients receiving errors, such as 8646
2. We see the errors in the event logs, run DBCC's to confirm the errors which then creates a dump file
3. We resolve by dropping/creating the index(es) affected
4. Re-run the DBCC checks and they come up clean.
A day or two later, the entire cycle will repeat against the same object/index. This problem exists on multiple different databases on multiple different servers, all with the same structure and hardware. Our SAN engineers say there are no issues they can see with the disks.
I would be very appreciative for any suggestions to better troubleshoot the issue, or, of course, a resolution would be magnificent!!
Here is as much information about our setup and errors experienced as I can think of to provide. Of course, if I am missing something just shout and I will include it. TIA.
We are running SQL 2005 enterprise x64 sp1 on Windows 2003 enterprise x64 sp1 as a 2-node active/passive cluster (native windows clustering). The hardware is HP Proliant DL585, 4 dual-core processors @ 2.8 GHz, 48 GB of RAM.
When clustering the servers, we pay special attention to ensure the NIC settings are as specified by published MS white papers both at the NIC level and as cluster configurations. I can provide those settings if necessary.
Other pertinent settings:
- We set the SQL Server's min/max setting to both be 40960 MB (40GB)
- MAXDOP to 2
- System DB's share two drives, one for data, one for log (all drives are RAID or 10)
- TempDB has 1 drive for data, one for log, and we modify TempDB to have as many data files as processors
- User DBs share multiple pairs of drives, each pair has a dedicated data drive and dedicated log drive
- Backups (litespeed) go to a dedicated drive
- Full-text catalogs go to a dedicated drive
- Maintenance jobs run every night and include: Reindex, update stats, backups and other miscellaneous jobs specific to our environment
- We set physical database files to grow by 200MB increments when over 2GB in size and we proactively grow each file overnight if the file's free space is < 10%
Unable to find index entry in index ID 1, of table 894626230, in database 'dbname'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.
Contents of the dump file:
This file is generated by Microsoft SQL Server
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Current time is 07:32:39 11/07/07
8 Unknown CPU 9., 2813 Mhz processor (s).
Windows NT 5.2 Build 3790 CSD Service Pack 1.
MemoryLoad = 92%
Total Physical = 49149 MB
Available Physical = 3913 MB
Total Page File = 60149 MB
Available Page File = 15932 MB
Total Virtual = 8388607 MB
Available Virtual = 8338218 MB
<Dbcc ID="0" Error="2511" Severity="16" State="1">Table error: Object ID 894626230, index ID 1, partition ID 7205
7596565782528, alloc unit ID 72057596808986624 (type In-row data). Keys out of order on page (1:487717), slots 3
<Dbcc ID="1" Error="2511" Severity="16" State="1">Table error: Object ID 894626230, index ID 1, partition ID 7205
7596565782528, alloc unit ID 72057596808986624 (type In-row data). Keys out of order on page (1:487717), slots 79