dbcc dbreindex problems

sql2k sp3
When running:
dbcc dbreindex ('dev.dbo.transdtl')
I get:
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 '8216076'.
The statement has been terminated.
Now this doesn't make sense. TransDTLKey is the PK column for this table. No
other unique constraints are defined. So I:
select transdtlkey
from transdtl
group by transdtlkey
having count(transdtlkey) > 1
and get back 0 records.
Ideas?
TIA, ChrisRSmells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might be
have been dreaming, though), I'd search the newsgroups archive as well...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
> sql2k sp3
> When running:
> dbcc dbreindex ('dev.dbo.transdtl')
> I get:
> 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 '8216076'.
> The statement has been terminated.
> Now this doesn't make sense. TransDTLKey is the PK column for this table. No
> other unique constraints are defined. So I:
> select transdtlkey
> from transdtl
> group by transdtlkey
> having count(transdtlkey) > 1
> and get back 0 records.
>
> Ideas?
> TIA, ChrisR|||You are correct:
Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511: [Microsoft][ODBC SQL
Server Driver][SQL Server]Table error: Object ID 1945773989, Index ID 0. Keys
out of order on page (1:817776), slots 57 and 58.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
1945773989, Index ID 16. Keys out of order on page (1:637458), slots 128 and
129.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in table 'TransDtl' (object ID 1945773989).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in database 'DEV'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_rebuild is the minimum
repair level for the errors found by DBCC CHECKDB (DEV ).
The problem is though that I ran dbcc checkdb with repair_rebuild and it
errors out because of this issue. So the Checkdb cant fix it and neither can
the Reindex. Any other ideas? A few years back at another job we had to:
Put the data into new tables.
Drop the old table.
Rename the new table.
to get rid of other strange behavior and it seemed to do the trick. I may
try this again here.
"Tibor Karaszi" wrote:
> Smells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might be
> have been dreaming, though), I'd search the newsgroups archive as well...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
> > sql2k sp3
> >
> > When running:
> >
> > dbcc dbreindex ('dev.dbo.transdtl')
> >
> > I get:
> >
> > 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 '8216076'.
> > The statement has been terminated.
> >
> > Now this doesn't make sense. TransDTLKey is the PK column for this table. No
> > other unique constraints are defined. So I:
> >
> > select transdtlkey
> > from transdtl
> > group by transdtlkey
> > having count(transdtlkey) > 1
> >
> > and get back 0 records.
> >
> >
> > Ideas?
> >
> > TIA, ChrisR
>
>|||ChrisR wrote:
> You are correct:
> Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> 1945773989, Index ID 0. Keys out of order on page (1:817776), slots
> 57 and 58. [Microsoft][ODBC SQL Server Driver][SQL Server]Table
> error: Object ID 1945773989, Index ID 16. Keys out of order on page
> (1:637458), slots 128 and 129.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0
> allocation errors and 2 consistency errors in table 'TransDtl'
> (object ID 1945773989). [Microsoft][ODBC SQL Server Driver][SQL
> Server]CHECKDB found 0 allocation errors and 2 consistency errors in
> database 'DEV'. [Microsoft][ODBC SQL Server Driver][SQL
> Server]repair_rebuild is the minimum repair level for the errors
> found by DBCC CHECKDB (DEV ).
>
> The problem is though that I ran dbcc checkdb with repair_rebuild and
> it errors out because of this issue. So the Checkdb cant fix it and
> neither can the Reindex. Any other ideas? A few years back at another
> job we had to:
> Put the data into new tables.
> Drop the old table.
> Rename the new table.
> to get rid of other strange behavior and it seemed to do the trick. I
> may try this again here.
>
If you script out the table and data, can you try using the
REPAIR_ALLOW_DATA_LOSS option with dbcc checkdb. You can do this under a
user transaction to examine the differences (or data loss) when compared
to the copy of the table. If you find the repairs sufficient, you can
update rows from the table copy and then insert missing rows from the
table copy to get the tables in sync.
You obviously want to script out this whole procedure before you commit
to tying up production data for too long. OTOH, if your production table
is corrupt, the sooner you fix it, the better.
--
David Gugick
Imceda Software
www.imceda.com|||Im lucky in that this is a development db.
"David Gugick" wrote:
> ChrisR wrote:
> > You are correct:
> >
> > Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> > 1945773989, Index ID 0. Keys out of order on page (1:817776), slots
> > 57 and 58. [Microsoft][ODBC SQL Server Driver][SQL Server]Table
> > error: Object ID 1945773989, Index ID 16. Keys out of order on page
> > (1:637458), slots 128 and 129.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0
> > allocation errors and 2 consistency errors in table 'TransDtl'
> > (object ID 1945773989). [Microsoft][ODBC SQL Server Driver][SQL
> > Server]CHECKDB found 0 allocation errors and 2 consistency errors in
> > database 'DEV'. [Microsoft][ODBC SQL Server Driver][SQL
> > Server]repair_rebuild is the minimum repair level for the errors
> > found by DBCC CHECKDB (DEV ).
> >
> >
> > The problem is though that I ran dbcc checkdb with repair_rebuild and
> > it errors out because of this issue. So the Checkdb cant fix it and
> > neither can the Reindex. Any other ideas? A few years back at another
> > job we had to:
> >
> > Put the data into new tables.
> > Drop the old table.
> > Rename the new table.
> >
> > to get rid of other strange behavior and it seemed to do the trick. I
> > may try this again here.
> >
> >
> If you script out the table and data, can you try using the
> REPAIR_ALLOW_DATA_LOSS option with dbcc checkdb. You can do this under a
> user transaction to examine the differences (or data loss) when compared
> to the copy of the table. If you find the repairs sufficient, you can
> update rows from the table copy and then insert missing rows from the
> table copy to get the tables in sync.
> You obviously want to script out this whole procedure before you commit
> to tying up production data for too long. OTOH, if your production table
> is corrupt, the sooner you fix it, the better.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||1. Read about the specific error numbers in the updated Books Online. The update includes detailed
explanations for all corruption types of errors, including suggested actions. Execute the DBCC CHECK
command from QA so you get all error numbers back (Maint Wiz only return error number from first
error that a command generates).
2. Generic recommendations:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:BE9354B5-AB7D-407E-ABCB-60E8A46233F2@.microsoft.com...
> You are correct:
> Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Table error: Object ID 1945773989, Index ID 0. Keys
> out of order on page (1:817776), slots 57 and 58.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> 1945773989, Index ID 16. Keys out of order on page (1:637458), slots 128 and
> 129.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in table 'TransDtl' (object ID 1945773989).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in database 'DEV'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_rebuild is the minimum
> repair level for the errors found by DBCC CHECKDB (DEV ).
>
> The problem is though that I ran dbcc checkdb with repair_rebuild and it
> errors out because of this issue. So the Checkdb cant fix it and neither can
> the Reindex. Any other ideas? A few years back at another job we had to:
> Put the data into new tables.
> Drop the old table.
> Rename the new table.
> to get rid of other strange behavior and it seemed to do the trick. I may
> try this again here.
>
> "Tibor Karaszi" wrote:
>> Smells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might
>> be
>> have been dreaming, though), I'd search the newsgroups archive as well...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
>> > sql2k sp3
>> >
>> > When running:
>> >
>> > dbcc dbreindex ('dev.dbo.transdtl')
>> >
>> > I get:
>> >
>> > 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 '8216076'.
>> > The statement has been terminated.
>> >
>> > Now this doesn't make sense. TransDTLKey is the PK column for this table. No
>> > other unique constraints are defined. So I:
>> >
>> > select transdtlkey
>> > from transdtl
>> > group by transdtlkey
>> > having count(transdtlkey) > 1
>> >
>> > and get back 0 records.
>> >
>> >
>> > Ideas?
>> >
>> > TIA, ChrisR
>>