Table Corrupt

[Vivek Amin]

Hello,

I got this message when i ran DBCC newalloc on one of my database

************************************************** *************
TABLE: tprxtx OBJID = 1355151873
INDID=1 FIRST=7608 ROOT=75375 DPAGES=8074 SORT=0
Msg 2543, Level 16, State 2
Table Corrupt: Extent structures are linked incorrectly; check the following extent: alloc pg#=60672 extent#=60712 previous extent# on this extent=32816 previous extent should be=60872
Data level: 1. 8074 Data Pages in 0 extents.
INDID=2 FIRST=7624 ROOT=25286 DPAGES=1 SORT=0
Indid : 2. 1097 Index Pages in 138 extents.
INDID=3 FIRST=7632 ROOT=32208 DPAGES=1 SORT=0
Indid : 3. 691 Index Pages in 88 extents.
INDID=4 FIRST=7640 ROOT=33272 DPAGES=1 SORT=0
Indid : 4. 638 Index Pages in 81 extents.
INDID=5 FIRST=7592 ROOT=21487 DPAGES=117 SORT=1
Indid : 5. 310 Index Pages in 39 extents.
TOTAL # of extents = 346
************************************************** *************

I went through the manual, the only option i have according to the manual is restore a database, but i can`t do that cause my old backup already has this problem.

Please help ASAP

Thanks
vIVEk.

[Jonathan Yang]

This kind of problem once drive me crazy!

1. From the Object ID: 1355151873 get table name.
2. then BCP out table (your talbe is some 5MB big, right).
3. drop and recreate the table and then BCP in data.

if in step 2, the BCP lasts forever, stop it using Ctrl+C. You have the BCP data file which may have already contained duplicate records and all records from the table. you can reproduce the table using the promary key info to get rid of the redundancy.

Be prepared that the SQL server won`t restart if it has problem on more than one table.

The last resort is to call microsoft for accident help.



On 8/4/98 1:20:45 PM, Vivek Amin wrote:
> Hello,

I got this message when i ran DBCC newalloc on one of my
> database

************************************************** *************

>
TABLE: tprxtx OBJID = 1355151873
INDID=1 FIRST=7608 ROOT=75375
> DPAGES=8074 SORT=0
Msg 2543, Level 16, State 2
Table Corrupt: Extent
> structures are linked incorrectly; check the following extent: alloc
> pg#=60672 extent#=60712 previous extent# on this extent=32816 previous
> extent should be=60872
Data level: 1. 8074 Data Pages in 0
> extents.
INDID=2 FIRST=7624 ROOT=25286 DPAGES=1 SORT=0
Indid : 2.
> 1097 Index Pages in 138 extents.
INDID=3 FIRST=7632 ROOT=32208
> DPAGES=1 SORT=0
Indid : 3. 691 Index Pages in 88 extents.
INDID=4
> FIRST=7640 ROOT=33272 DPAGES=1 SORT=0
Indid : 4. 638 Index Pages in
> 81 extents.
INDID=5 FIRST=7592 ROOT=21487 DPAGES=117 SORT=1
Indid :
> 5. 310 Index Pages in 39 extents.
TOTAL # of extents =
> 346
************************************************** *************

I
> went through the manual, the only option i have according to the manual is
> restore a database, but i can`t do that cause my old backup already has
> this problem.

Please help ASAP

Thanks
vIVEk.

[Chuck Lucking]

On 8/4/98 1:37:35 PM, Jonathan Yang wrote:
> This kind of problem once drive me crazy!

1. From the Object ID:
> 1355151873 get table name.
2. then BCP out table (your talbe is some 5MB
> big, right).
3. drop and recreate the table and then BCP in data.

Jonathan is correct, however before dropping the table try truncating the table and repeating the DBCC. By truncating you deallocate the pages, which may correct the problem.
Also if you have space inside your database you may want to select into rather than BCP.

Chuck

[Vivek Amin]

Thanks Guys
It worked !!

On 8/4/98 1:56:54 PM, Chuck Lucking wrote:
> On 8/4/98 1:37:35 PM, Jonathan Yang wrote:
> This kind of problem once
> drive me crazy!

1. From the Object ID:
> 1355151873 get table
> name.
2. then BCP out table (your talbe is some 5MB
> big, right).
3.
> drop and recreate the table and then BCP in data.

Jonathan is correct,
> however before dropping the table try truncating the table and repeating
> the DBCC. By truncating you deallocate the pages, which may correct the
> problem.
Also if you have space inside your database you may want to
> select into rather than BCP.

Chuck