SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Extra space in tables with dbcc checkdb errors


Extra space in tables with dbcc checkdb errors

Author
Message
Nicole Garris
Nicole Garris
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 143
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87415 Visits: 45272
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

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


Nicole Garris
Nicole Garris
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 143
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.
delamater
delamater
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 35
Did you finally resolve this issue?
Nicole Garris
Nicole Garris
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 143
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.
Joie Andrew
Joie Andrew
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2315 Visits: 2032
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"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search