June 27, 2011 at 12:42 pm
Running SQL Server 2000 standard 8.0.2055 (SP 4 +) on Windows 2003 standard. Server has low use to basically no use. Database has been growing since 2004, from about 10 MB to currently 292 MB (data 270 MB, log 23 MB), however data size has grown less than 10%. Recently have had two instances of data corruption (see dbcc errors below). My theory is that ghost rows are not being deleted and somehow that causes another bug which is the corruption. I'd like to know if my theory makes sense or not.
The primary activity on the server is a copy program that copies 9 tables from a SQL 2008 database to the database in question using a delete all and then insert commands. Copy program runs every night.
To resolve the problem I truncated the 9 tables and then re-ran the copy program. This reduced the data KB (per sp_spaceused) from 21296 KB to 2768 KB. Index size remained roughly the same.
First instance of corruption produced errors like this in the FITB_EMPLOYEE table. I did not attempt to fix, I simply restored from the previous backup.
...
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 0, index ID 0, page ID (1:6719). The PageId in the page header = (0:0).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 1442104178, index ID 2. Page (1:6480) is missing references from parent (unknown) and previous (page (0:0)) nodes. Possible bad root entry in sysindexes.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Page (1:6481) allocated to object ID 1442104178, index ID 2 was not seen. Page may be invalid or have incorrect object ID information in its header.
...
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 5 consistency errors not associated with any single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 64 consistency errors in table 'FITB_EMPLOYEE' (object ID 1442104178).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 69 consistency errors in database 'DOFSHR'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DOFSHR ).
This is the entire report produced the second instance of data corruption. In this case, when we re-ran DBCC Checkdb, it found no errors. There may have been a rebuild index in between.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 1442104178, index ID 2. Page (1:369) is missing references from parent (unknown) and previous (page (0:0)) nodes. Possible bad root entry in sysindexes.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in table 'FITB_EMPLOYEE' (object ID 1442104178).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in database 'DOFSHR'.
[Microsoft][ODBC SQL Server Driver][SQL Server] repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (DOFSHR ).
Thank you in advance for you help and comments.l
June 27, 2011 at 3:05 pm
Corruption is a hardware issue. Something is mangling the pages outside of SQL Server. Do some diagnostics on the IO subsystem and consider moving that database elsewhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2011 at 3:54 pm
I doubt its a hardware issue. Its a virtualized server and no other server on the host is experiencing problems. The storage is on a SAN and again, none of our other servers/storage have any problem. We are not seeing any alerts for the host nor for the SAN.
January 12, 2012 at 2:17 pm
Did you finally resolve this issue?
January 12, 2012 at 2:27 pm
Evidence still seems to indicate the copy program (written using SSIS) is instrumental in the corruption, in other words, corruption only occurs when the copy program runs. It is still occurring from time to time. So far corruption has only occurred in the indexes, never in data. We engaged Microsoft support but only received the recommendation that the indexes be dropped at the beginning of the copy program and rebuilt at the end. (SQL Server 2000 has been out of support for some time now.)
This is a virtual server running on a SAN. The SAN is not kicking out any errors.
Sorry I don't have anything more to help you.
January 13, 2012 at 5:33 am
Object ID 1442104178, index ID 2
Isn't index ID 2 indicate a non-clustered index? When was the last time indexes were rebuilt on the database?
Joie Andrew
"Since 1982"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply