<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 7,2000
»
Data Corruption
»
Extra space in tables with dbcc checkdb...
Extra space in tables with dbcc checkdb errors
Rate Topic
Display Mode
Topic Options
Author
Message
Nicole Garris
Nicole Garris
Posted Monday, June 27, 2011 12:42 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, February 13, 2013 2:49 PM
Points: 23,
Visits: 109
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
GilaMonster
GilaMonster
Posted Monday, June 27, 2011 3:05 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651,
Visits: 29,903
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
Nicole Garris
Nicole Garris
Posted Monday, June 27, 2011 3:54 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, February 13, 2013 2:49 PM
Points: 23,
Visits: 109
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
delamater
delamater
Posted Thursday, January 12, 2012 2:17 PM
Forum 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
Nicole Garris
Nicole Garris
Posted Thursday, January 12, 2012 2:27 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, February 13, 2013 2:49 PM
Points: 23,
Visits: 109
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
Joie Andrew
Joie Andrew
Posted Friday, January 13, 2012 5:33 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Monday, May 06, 2013 5:58 AM
Points: 535,
Visits: 1,010
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 »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
