Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extra space in tables with dbcc checkdb errors Expand / Collapse
Author
Message
Posted Monday, June 27, 2011 12:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 25, 2014 2:09 PM
Points: 27, Visits: 138
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

Post #1132378
Posted Monday, June 27, 2011 3:05 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1132506
Posted Monday, June 27, 2011 3:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 25, 2014 2:09 PM
Points: 27, Visits: 138
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.
Post #1132531
Posted Thursday, January 12, 2012 2:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 13, 2012 11:37 AM
Points: 4, Visits: 34
Did you finally resolve this issue?
Post #1235238
Posted Thursday, January 12, 2012 2:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 25, 2014 2:09 PM
Points: 27, Visits: 138
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.
Post #1235245
Posted Friday, January 13, 2012 5:33 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:02 AM
Points: 709, Visits: 1,422
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"
Post #1235543
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse