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
![]() |
0 |
![]() |
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
![]() |
0 |
![]() |
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--
![]() |
0 |
![]() |
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 >>
![]() |
0 |
![]() |
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 > >> >
![]() |
0 |
![]() |
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)
![]() |
0 |
![]() |
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
![]() |
0 |
![]() |