TABLE: TPDISPLAY                            OBJID =
1024722703

INDID=1                FIRST=2305896 ROOT=2307891 SORT=1

The missing OAM entry has been inserted.

Server Message:  Number  15083, Severity  10

Server 'SAFPUR3CDBS01', Line 1:

109 OAM entries with zero used/unused page counts have been
deleted for objid 1024722703 indid 0.

The oam counts for objid 1024722703 indid 0 are corrected.

                Data level: 1.  645467 Data pages allocated
and 157085 Extents allocated.

Server Message:  Number  15083, Severity  10

Server 'SAFPUR3CDBS01', Line 1:

3 OAM entries with zero used/unused page counts have been
deleted for objid 1024722703 indid 1.

The oam counts for objid 1024722703 indid 1 are corrected.

                Indid         : 1.  27965 Index pages
allocated and 6967 Extents allocated.

INDID=2                FIRST=2365000 ROOT=2326002 SOR

Did you have a question about this?

BTW, the 15083 messages are not errors.  0/0 entries
occur normally as space is deallocated from a table.
They are likely not connected at all with the power failure.

-bret


On 12/15/2010 6:55 AM, Lee wrote:
> TABLE: TPDISPLAY                            OBJID =
> 1024722703
>
> INDID=1                FIRST=2305896 ROOT=2307891 SORT=1
>
> The missing OAM entry has been inserted.
>
> Server Message:  Number  15083, Severity  10
>
> Server 'SAFPUR3CDBS01', Line 1:
>
> 109 OAM entries with zero used/unused page counts have been
> deleted for objid 1024722703 indid 0.
>
> The oam counts for objid 1024722703 indid 0 are corrected.
>
>                  Data level: 1.  645467 Data pages allocated
> and 157085 Extents allocated.
>
> Server Message:  Number  15083, Severity  10
>
> Server 'SAFPUR3CDBS01', Line 1:
>
> 3 OAM entries with zero used/unused page counts have been
> deleted for objid 1024722703 indid 1.
>
> The oam counts for objid 1024722703 indid 1 are corrected.
>
>                  Indid         : 1.  27965 Index pages
> allocated and 6967 Extents allocated.
>
> INDID=2                FIRST=2365000 ROOT=2326002 SOR

This is a multi-part message in MIME format.

---=_forums-1-dub4d090109
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 7bit

Thanks Bret,
Yes sorry I had a question and got click happy :)
Your comment was helpful.
However I've since found more errors, which to me looks like
corruption on at least one table.
Will attach output.

We fixed quite a lot of the allocation errors with DBCC's,
but there's one table that lost 4mil rows.
This table here shows 5.8 million rows with select count(*)
but when using SELECT INTO it only copies 1004183 rows.

I can see forward and backpointers when running dbcc
tablealloc with the fix option, but I now want to fix the
linkage errors for this table.

Any idea how I can see the rowid for a specific page that
has corruption problems?

Please see attached files
Tx
Lee

> Did you have a question about this?
>
> BTW, the 15083 messages are not errors.  0/0 entries
> occur normally as space is deallocated from a table.
> They are likely not connected at all with the power
> failure.
>
> -bret
>
>
> On 12/15/2010 6:55 AM, Lee wrote:
> > TABLE: TPDISPLAY                            OBJID =
> > 1024722703
> >
> > INDID=1                FIRST=2305896 ROOT=2307891 SORT=1
> >
> > The missing OAM entry has been inserted.
> >
> > Server Message:  Number  15083, Severity  10
> >
> > Server 'SAFPUR3CDBS01', Line 1:
> >
> > 109 OAM entries with zero used/unused page counts have
> > been deleted for objid 1024722703 indid 0.
> >
> > The oam counts for objid 1024722703 indid 0 are
> corrected. >
> >                  Data level: 1.  645467 Data pages
> > allocated and 157085 Extents allocated.
> >
> > Server Message:  Number  15083, Severity  10
> >
> > Server 'SAFPUR3CDBS01', Line 1:
> >
> > 3 OAM entries with zero used/unused page counts have
> > been deleted for objid 1024722703 indid 1.
> >
> > The oam counts for objid 1024722703 indid 1 are
> corrected. >
> >                  Indid         : 1.  27965 Index pages
> > allocated and 6967 Extents allocated.
> >
> > INDID=2                FIRST=2365000 ROOT=2326002 SOR
>

---=_forums-1-dub4d090109
Content-Type: text/plain; name="linkage errors.txt"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="linkage errors.txt"

VGFibGUgQ29ycnVwdDogUGFnZSBsaW5rYWdlIGlzIG5vdCBjb25zaXN0ZW50
OyBjaGVjayB0aGUgZm9sbG93aW5nIHBhZ2VzOiAoY3VycmVudCBwYWdlIz00
ODc1NjQ3MDsgIHBhZ2UjIHBvaW50aW5nIHRvIHRoaXMgcGFnZT00MjkzNDgy
NTsgcHJldmlvdXMgcGFnZSMgaW5kaWNhdGVkIGluIHRoaXMgcGFnZT00ODc1
NjM0OSkgDQoNClRhYmxlIENvcnJ1cHQ6IFBhZ2UgbGlua2FnZSBpcyBub3Qg
Y29uc2lzdGVudDsgY2hlY2sgdGhlIGZvbGxvd2luZyBwYWdlczogKGN1cnJl
bnQgcGFnZSM9MjM1MTEyODsgIHBhZ2UjIHBvaW50aW5nIHRvIHRoaXMgcGFn
ZT0zMTI1NTgwNTsgcHJldmlvdXMgcGFnZSMgaW5kaWNhdGVkIGluIHRoaXMg
cGFnZT00ODc1NjU3NikgDQoNCg==

---=_forums-1-dub4d090109
Content-Type: text/plain; name="read_errors.txt"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="read_errors.txt"

MDE6MDAwMDA6MDA1OTU6MjAxMC8xMi8wOCAyMTozMzo0Ni43NyBzZXJ2ZXIg
IFN0YXJ0aW5nIGRpYWdub3N0aWNzIGZvciByZWFkIGZhaWx1cmU6DQoNCjAx
OjAwMDAwOjAwNTk1OjIwMTAvMTIvMDggMjE6MzM6NDYuNzcgc2VydmVyICBE
ZXZpY2UgSW5mb3JtYXRpb246DQoNCiAgICAgICAgICAgICAgICAgICAgICAg
ICBEZXZpY2UgbnVtYmVyID0gMTINCg0KICAgICAgICAgICAgICAgICAgICAg
ICAgIFBoeW5hbWUgPSAvc3liX2RhdGFkZXYvZ2QvR0RTQUZQUk9EX0QwMy5E
QVQNCg0KMDE6MDAwMDA6MDA1OTU6MjAxMC8xMi8wOCAyMTozMzo0Ni43NyBz
ZXJ2ZXIgIEJ1ZmZlciBJbmZvcm1hdGlvbjoNCg0KICAgICAgICAgICAgICAg
ICAgICAgICAgIEJ1ZiBhZGRyID0gMHgxMDNhMTRhMDljMCwgTWFzcyBhZGRy
ID0gMHgxMDNhMTRhMDljMA0KDQogICAgICAgICAgICAgICAgICAgICAgICAg
QnVmIHBhZ2VubyA9IDQ4NzU2NTg0LCBNYXNzIHBhZ2VubyA9IDQ4NzU2NTg0
LCBkYmlkID0gNw0KDQowMTowMDAwMDowMDU5NToyMDEwLzEyLzA4IDIxOjMz
OjQ2Ljc3IHNlcnZlciAgDQoNCiAgICAgICAgICAgICAgICAgICAgICAgICBC
dWYgdmlydHBnID0gMjE3MzE1MTc2LCBNYXNzIHZpcnRwZyA9IDIxNzMxNTE3
Ng0KDQogICAgICAgICAgICAgICAgICAgICAgICAgQnVmIHN0YXQgPSAweDEs
IE1hc3Mgc3RhdCA9IDB4MTAwOA0KDQogICAgICAgICAgICAgICAgICAgICAg
ICAgTWFzcyBrZWVwID0gMSwgTWFzcyBhd2FpdGVkID0gMA0KDQowMTowMDAw
MDowMDU5NToyMDEwLzEyLzA4IDIxOjMzOjQ2Ljc3IHNlcnZlciAgUGFnZSBJ
bmZvcm1hdGlvbiBmcm9tIGZpcnN0IHJlYWQgYXR0ZW1wdDogUGFnZSByZWFk
IGZyb20gZGlzayBwcGFnZW5vID0gNDg3NTY1ODQsIHBvYmppZCA9IDc5NzU1
ODIyNSwgcGluZGlkID0gMCBwbmV4dHBnID0gNDg3NTY1ODUsIHBwcmV2cGcg
PSA0ODc1NjU4MyBwbGV2ZWwgPSAwLCBwc3RhdCA9IDB4MTAxIHB0c19oaSA9
IDUxOCwgcHRzX2xvID0gMTI3ODI4NTkwNQ0KDQowMTowMDAwMDowMDU5NToy
MDEwLzEyLzA4IDIxOjMzOjQ2Ljc3IHNlcnZlciAgUGFnZSBJbmZvcm1hdGlv
biBmcm9tIHNlY29uZCByZWFkIGF0dGVtcHQ6IFBhZ2UgcmVhZCBmcm9tIGRp
c2sgcHBhZ2VubyA9IDQ4NzU2NTg0LCBwb2JqaWQgPSA3OTc1NTgyMjUsIHBp
bmRpZCA9IDAgcG5leHRwZyA9IDQ4NzU2NTg1LCBwcHJldnBnID0gNDg3NTY1
ODMgcGxldmVsID0gMCwgcHN0YXQgPSAweDEwMSBwdHNfaGkgPSA1MTgsIHB0
c19sbyA9IDEyNzgyODU5MDUNCg0KMDE6MDAwMDA6MDA1OTU6MjAxMC8xMi8w
OCAyMTozMzo0Ni43NyBzZXJ2ZXIgIFNERVMgSW5mb3JtYXRpb246DQoNCiAg
ICAgICAgICAgICAgICAgICAgICAgICBkYmlkID0gNywgb2JqaWQgPSAxMjY2
ODE5NTc1LCBzY3VyLnBhZ2VpZCA9IDQ4NzU2NTg0DQoNCiAgICAgICAgICAg
ICAgICAgICAgICAgICBzc3RhdCA9IDB4MCwgc3N0YXQyID0gMHgwDQoNCiAg
ICAgICAgICAgICAgICAgICAgICAgICBzdWlkID0gMSwgY2FjaGVpZCA9IDAN
Cg0KMDE6MDAwMDA6MDA1OTU6MjAxMC8xMi8wOCAyMTozMzo0Ni43NyBzZXJ2
ZXIgIFBTUyBJbmZvcm1hdGlvbjoNCg0KICAgICAgICAgICAgICAgICAgICAg
ICAgIHBzdGF0ID0gMHgxMDAwMCwgcGN1cmRiID0gNywgcHNwaWQgPSA1OTUN
Cg0KICAgICAgICAgICAgICAgICAgICAgICAgIHAyc3RhdCA9IDB4MTAwMCwg
cDNzdGF0ID0gMHg4MDANCg0KICAgICAgICAgICAgICAgICAgICAgICAgIHBs
YXN0ZXJyb3IgPSAwLCBwcmV2ZXJyb3IgPSAwLCBwYXR0ZW50aW9uID0gMA0K
DQowMTowMDAwMDowMDU5NToyMDEwLzEyLzA4IDIxOjMzOjQ2Ljc3IHNlcnZl
ciAgRW5kIGRpYWdub3N0aWNzIGZvciByZWFkIGZhaWx1cmU6DQoNCiANCg0K

---=_forums-1-dub4d090109--

By "rowid", do you mean you have a column in the table named
rowid?

dbcc page(dbname, pageno, 1)  will print out the contents
of a page formated with the data rows seperated  (print option 2
prints out the whole page as a block of raw hex).

The row format consists of a few bytes of overhead, then the contents
of all the fixed-length columns concatenated together ordered by
syscolumns.colid, then a couple bytes holding the total row length,
then all the data from the variable-length columns, then an offset table
that describes the length of each variable column data.

So if your primary key/rowid field is fixed length, you should be able
to pick it out of the fixed length part of the row fairly easily with
a little practice.  A helpful trick is to create a table with
the same structure and insert a row that has a unique value for each
column [insert mytable values ("a", "bbb", "c", 1, "d", 2) ]
, get dbcc page output on that and use that to find the right
offsets for the data you are looking for.

Sounds like the database was created on a file system that cached writes 
and so lost some ommitted updates when the power failed?

-bret

On 12/15/2010 10:55 AM, Lee wrote:
> Thanks Bret,
> Yes sorry I had a question and got click happy :)
> Your comment was helpful.
> However I've since found more errors, which to me looks like
> corruption on at least one table.
> Will attach output.
>
> We fixed quite a lot of the allocation errors with DBCC's,
> but there's one table that lost 4mil rows.
> This table here shows 5.8 million rows with select count(*)
> but when using SELECT INTO it only copies 1004183 rows.
>
> I can see forward and backpointers when running dbcc
> tablealloc with the fix option, but I now want to fix the
> linkage errors for this table.
>
> Any idea how I can see the rowid for a specific page that
> has corruption problems?
>
> Please see attached files
> Tx
> Lee
>
>> Did you have a question about this?
>>
>> BTW, the 15083 messages are not errors.  0/0 entries
>> occur normally as space is deallocated from a table.
>> They are likely not connected at all with the power
>> failure.
>>
>> -bret
>>
>>
>> On 12/15/2010 6:55 AM, Lee wrote:
>>> TABLE: TPDISPLAY                            OBJID =
>>> 1024722703
>>>
>>> INDID=1                FIRST=2305896 ROOT=2307891 SORT=1
>>>
>>> The missing OAM entry has been inserted.
>>>
>>> Server Message:  Number  15083, Severity  10
>>>
>>> Server 'SAFPUR3CDBS01', Line 1:
>>>
>>> 109 OAM entries with zero used/unused page counts have
>>> been deleted for objid 1024722703 indid 0.
>>>
>>> The oam counts for objid 1024722703 indid 0 are
>> corrected.>
>>>                   Data level: 1.  645467 Data pages
>>> allocated and 157085 Extents allocated.
>>>
>>> Server Message:  Number  15083, Severity  10
>>>
>>> Server 'SAFPUR3CDBS01', Line 1:
>>>
>>> 3 OAM entries with zero used/unused page counts have
>>> been deleted for objid 1024722703 indid 1.
>>>
>>> The oam counts for objid 1024722703 indid 1 are
>> corrected.>
>>>                   Indid         : 1.  27965 Index pages
>>> allocated and 6967 Extents allocated.
>>>
>>> INDID=2                FIRST=2365000 ROOT=2326002 SOR
>>

Hello Bret,

This was extremely helpful in my attempt to find which
particular record I am working with. The initial dbcc page()
on that table was very hard to read.

I was trying to see which record/s in the table sits on the
affected page with page linkage errors.
On the particular page, there were messages suggesting that
the backward pointer of the affected page is referring to a
different page than the one being scanned.

My question now is this: when a table gets corrupted with
page linkage errors, can one ever fix the linkage problems
for the table itself, by updating system tables?
Or will it always be a process of bluffing sysindexes by
updating the FIRST column for indid 0 with the page after
the problem page and bcp'ing the data out from there onwards
in batches by updating FIRST again for the next chunk and so
on - in other words jumping the bad backward pointers?

In my case, I recovered the 4 million rows by doing this in
batches, and moving the FIRST page each time.
So there were a few rows lost, but we accepted this.
However, I thought it would be great if I can just update
the backward pointer on the system tables with the number of
the page as scanned in the chain.
L
> By "rowid", do you mean you have a column in the table
> named rowid?
>
> dbcc page(dbname, pageno, 1)  will print out the contents
> of a page formated with the data rows seperated  (print
> option 2 prints out the whole page as a block of raw hex).
>
> The row format consists of a few bytes of overhead, then
> the contents of all the fixed-length columns concatenated
> together ordered by syscolumns.colid, then a couple bytes
> holding the total row length, then all the data from the
> variable-length columns, then an offset table that
> describes the length of each variable column data.
>
> So if your primary key/rowid field is fixed length, you
> should be able to pick it out of the fixed length part of
> the row fairly easily with a little practice.  A helpful
> trick is to create a table with the same structure and
> insert a row that has a unique value for each column
> [insert mytable values ("a", "bbb", "c", 1, "d", 2) ] ,
> get dbcc page output on that and use that to find the
> right offsets for the data you are looking for.
>
> Sounds like the database was created on a file system that
> cached writes  and so lost some ommitted updates when the
> power failed?
>
> -bret
>
> On 12/15/2010 10:55 AM, Lee wrote:
> > Thanks Bret,
> > Yes sorry I had a question and got click happy :)
> > Your comment was helpful.
> > However I've since found more errors, which to me looks
> > like corruption on at least one table.
> > Will attach output.
> >
> > We fixed quite a lot of the allocation errors with
> > DBCC's, but there's one table that lost 4mil rows.
> > This table here shows 5.8 million rows with select
> > count(*) but when using SELECT INTO it only copies
> 1004183 rows. >
> > I can see forward and backpointers when running dbcc
> > tablealloc with the fix option, but I now want to fix
> > the linkage errors for this table.
> >
> > Any idea how I can see the rowid for a specific page
> > that has corruption problems?
> >
> > Please see attached files
> > Tx
> > Lee
> >
> >> Did you have a question about this?
> >>
> >> BTW, the 15083 messages are not errors.  0/0 entries
> >> occur normally as space is deallocated from a table.
> >> They are likely not connected at all with the power
> >> failure.
> >>
> >> -bret
> >>
> >>
> >> On 12/15/2010 6:55 AM, Lee wrote:
> >>> TABLE: TPDISPLAY                            OBJID =
> >>> 1024722703
> >>>
> >>> INDID=1                FIRST=2305896 ROOT=2307891
> SORT=1 >>>
> >>> The missing OAM entry has been inserted.
> >>>
> >>> Server Message:  Number  15083, Severity  10
> >>>
> >>> Server 'SAFPUR3CDBS01', Line 1:
> >>>
> >>> 109 OAM entries with zero used/unused page counts have
> >>> been deleted for objid 1024722703 indid 0.
> >>>
> >>> The oam counts for objid 1024722703 indid 0 are
> >> corrected.>
> >>>                   Data level: 1.  645467 Data pages
> >>> allocated and 157085 Extents allocated.
> >>>
> >>> Server Message:  Number  15083, Severity  10
> >>>
> >>> Server 'SAFPUR3CDBS01', Line 1:
> >>>
> >>> 3 OAM entries with zero used/unused page counts have
> >>> been deleted for objid 1024722703 indid 1.
> >>>
> >>> The oam counts for objid 1024722703 indid 1 are
> >> corrected.>
> >>>                   Indid         : 1.  27965 Index
> pages >>> allocated and 6967 Extents allocated.
> >>>
> >>> INDID=2                FIRST=2365000 ROOT=2326002 SOR
> >>
>

After further investigation, I see this:

pprevpg pointer for page 48756470 does not point to previous
page in chain as scanned.  pprevpg pointer = 48756349,
previous page as scanned = 42934825.

Page : 48756470

TABLE: t_display        OBJID = 1024722703
INDID=0     FIRST=2305896    ROOT=42109105    SORT=1
INDID=2     FIRST=2365000    ROOT=2326002     SORT=0
TOTAL # of extents = 0
Table Corrupt: Page linkage is not consistent; check the
following pages: (current page#=48756470;  page# pointing to
this page=42934825; previous page# indicated in this
page=48756349)
Table Corrupt: Page linkage is not consistent; check the
following pages: (current page#=2351128;  page# pointing to
this page=31255805; previous page# indicated in this
page=48756576)

On 12/21/2010 1:05 AM, Lee wrote:
> Hello Bret,
>
> This was extremely helpful in my attempt to find which
> particular record I am working with. The initial dbcc page()
> on that table was very hard to read.
>
> I was trying to see which record/s in the table sits on the
> affected page with page linkage errors.
> On the particular page, there were messages suggesting that
> the backward pointer of the affected page is referring to a
> different page than the one being scanned.
>
> My question now is this: when a table gets corrupted with
> page linkage errors, can one ever fix the linkage problems
> for the table itself, by updating system tables?
> Or will it always be a process of bluffing sysindexes by
> updating the FIRST column for indid 0 with the page after
> the problem page and bcp'ing the data out from there onwards
> in batches by updating FIRST again for the next chunk and so
> on - in other words jumping the bad backward pointers?
>
> In my case, I recovered the 4 million rows by doing this in
> batches, and moving the FIRST page each time.
> So there were a few rows lost, but we accepted this.
> However, I thought it would be great if I can just update
> the backward pointer on the system tables with the number of
> the page as scanned in the chain.
> L


Hi Lee,

It can't be done by updating system tables as the page pointers
aren't stored in tables (with the exception of the first and root
page pointers in sysindexes/syspartitions).  The pointers are
stored just in the page header of each page.

There is no simple command in ASE for modifying these.  It could be done
using a hex editor directly against the device file, though.
(Look at the dbcc page output as raw hex, use the editor to search
for the pattern from the header, find the 4 bytes that match the
bad prevpage value and update them to the desired value.).

How are you determining that you have lost rows?

-bret