<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
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Advertise
Write for us
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 7,2000
»
Data Corruption
»
0 allocation, 1 consistency error --...
29 posts, Page 3 of 3
««
«
1
2
3
0 allocation, 1 consistency error -- repair_rebuild min repair level didn’t fix
Rate Topic
Display Mode
Topic Options
Author
Message
Zivio!
Zivio!
Posted Sunday, January 24, 2010 12:02 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, March 03, 2010 6:12 PM
Points: 21,
Visits: 47
Thanks for all the troubleshooting help.
... so, there's no hope of fixing the database w/ "only" 1 consistency error?
I understand that we need to correct the underlying cause of the error, but once we do, can the restored database be fixed and then brought back up?
What other recourse do I have ... I've seen some postings discussing creating fresh new database and importing in all tables/records.
Post #852766
Paul Randal
Paul Randal
Posted Sunday, January 24, 2010 12:12 PM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 12:15 PM
Points: 1,526,
Visits: 1,209
You should be able to manually rebuild the clustered index and it fixes it. Let me know if it doesn't and we can discuss (mostly unpalatable) options.
Paul Randal
CEO,
SQLskills.com
- Need a database maintenance/ops/HA/DR audit? Send me email!
Blog:
www.SQLskills.com/blogs/paul
Twitter:
@PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of
TechNet Magazine
and
SQL Server Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #852767
Zivio!
Zivio!
Posted Sunday, January 24, 2010 1:59 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, March 03, 2010 6:12 PM
Points: 21,
Visits: 47
Guess we're on to the unpalatable options ...
DBCC DBREINDEX ('inv_period_usage') resulted in:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '122444'.
The statement has been terminated.
Post #852776
Paul Randal
Paul Randal
Posted Sunday, January 24, 2010 2:06 PM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 12:15 PM
Points: 1,526,
Visits: 1,209
That would be why the repair_rebuild failed too. You won't be able to fix this until you remove/fix the duplicate key values. Google should give you plenty of scripts to help you with that. Then the rebuild should work.
Paul Randal
CEO,
SQLskills.com
- Need a database maintenance/ops/HA/DR audit? Send me email!
Blog:
www.SQLskills.com/blogs/paul
Twitter:
@PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of
TechNet Magazine
and
SQL Server Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #852777
Zivio!
Zivio!
Posted Sunday, January 24, 2010 2:29 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, March 03, 2010 6:12 PM
Points: 21,
Visits: 47
Great! I'll see what I can find.
Thanks again for all your help!
Post #852782
Zivio!
Zivio!
Posted Sunday, January 24, 2010 4:11 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, March 03, 2010 6:12 PM
Points: 21,
Visits: 47
Found a single duplicate record in inv_period_usage and deleted manually from DB.
DBCC DBREINDEX('inv_period_usage') executed without error ...
AND, DBCC CHECKDB now comes up clean, no errors!
Now I need to investigate the I/O Subsystem errors and also see if I can bring a database from 1/9/2010 all the way thru 1/22/2010 [end of business Friday] ... current test database that now comes up good was only thru 1/21/2010. But even if this is unsuccessful, we'd be faced only with manually re-inputting one day's transactions.
I can't thank you enough, Paul!
Post #852787
Paul Randal
Paul Randal
Posted Sunday, January 24, 2010 5:27 PM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 12:15 PM
Points: 1,526,
Visits: 1,209
No problem - beers are on you at PASS
Paul Randal
CEO,
SQLskills.com
- Need a database maintenance/ops/HA/DR audit? Send me email!
Blog:
www.SQLskills.com/blogs/paul
Twitter:
@PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of
TechNet Magazine
and
SQL Server Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #852795
GilaMonster
GilaMonster
Posted Sunday, January 24, 2010 11:29 PM
SSChampion
Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 17,130,
Visits: 12,234
Just an addendum, now that the crisis has passed...
From what I saw in the list of indexes, you may have a few too many indexes on that table (though too many is subjective) and, based on the names, duplicate indexes.
It may be worth looking into when you have time.
Gail Shaw
We walk in the dark places no others will enter
We stand on the bridge and none may pass
Post #852842
Zivio!
Zivio!
Posted Sunday, January 24, 2010 11:49 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, March 03, 2010 6:12 PM
Points: 21,
Visits: 47
GilaMonster (1/24/2010)
Just an addendum, now that the crisis has passed...
From what I saw in the list of indexes, you may have a few too many indexes on that table (though too many is subjective) and, based on the names, duplicate indexes.
It may be worth looking into when you have time.
Interesting observation ... Unfortunately, I have no input to the design of the database. The database was developed by Activant, Inc. and is an integral part of their "Prophet 21" wholesale distribution enterprise software (www.p21.com).
The crisis for me isn't
quite
over, as I still need to get the I/O Subsystem issues resolved as well as upgrade to SQL 2005.
In the meantime, I've been checking out all the great resources at www.sqlskills.com and just listened to Paul's interview re: database corruption. Need to get up to speed on torn page detection, review our backup/restore strategy (can definitely be simplified/improved) and some agent alerts, etc. Baby steps for now ...
I can't adequately express my thanks for your time, expertise and willingness to help.
Zivio!
Karl Styrsky
Post #852849
« Prev Topic
|
Next Topic »
29 posts, Page 3 of 3
««
«
1
2
3
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-2010 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use
