dbccdb checkalloc, checkdb, check catalog failures practicals

Fixed checkdb errors:

Keys of index id 2 for table 'sysattributes' in leaf page not in proper order. Drop and recreate the index. (index page 14833)Msg 7928, Level 18, State 1:Line 1:Index ncsysattributes is not consistent; found 2 leaf rows but 20 data rows. Drop and recreate the index.Checking table 'dbcc_types' (object ID 752002679): Logical page size is 2048 bytes.

1> sp_fixindex dbccdb, sysattributes,22>00:00000:00012:2008/08/06 12:52:40.59 server dbcc dbrepair: Non-clustered index restored for dbccdb..sysattributes.There are 20.000000 rows in 1 pages for object 'sysattributes'.Non-clustered index successfully restored for object 'sysattributes' in 'dbccdb' database.DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.(return status = 0)1>

1> dbcc checktable(sysattributes)2>Checking table 'sysattributes' (object ID 21): Logical page size is 2048 bytes.
Checking partition 'sysattributes_21' (partition ID 21) of table 'sysattributes'. The logical page size of this table is 2048 bytes.The total number of data pages in partition 'sysattributes_21' (partition ID 21) is 1.Partition 'sysattributes_21' (partition ID 21) has 20 data rows.The total number of empty pages (with all deleted rows) in patition 'sysattributes_21' (partition ID 21) is 0.The total number of pages in partition 'sysattributes_21' (partition ID 21) which could be garbage collected to free up some space is 0.The total number of deleted rows in partition 'sysattributes_21' (partition ID 21) is 0.The total number of pages in partition 'sysattributes_21' (partition ID 21) with more than 50 percent garbage is 0.The total number of pages in partition 'sysattributes_21' (partition ID 21) with more than 50 percent insert free space is 1.
The total number of data pages in this table is 1.The total number of pages with more than 50 percent insert free space is 1.Table has 20 data rows.DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.1>



Msg 692, Level 16, State 1:Line 1:Uninitialized logical page '30957' was read while accessing database 'dbccdb' (31515), object 'sysprocedures' (5), index 'sysprocedures' (0), partition 'sysprocedures_5' (5). Please contact Sybase Technical Support.

1> sp_fixindex dbccdb, sysprocedures,22>00:00000:00012:2008/08/06 13:08:14.56 server dbcc dbrepair: Clustered index restored for dbccdb..sysprocedures.There are 1628.000000 rows in 816 pages for object 'sysprocedures'.Clustered index successfully restored for object 'sysprocedures' in 'dbccdb' database.DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.(return status = 0)1> dbcc checktable(sysprocedures)2>Checking table 'sysprocedures' (object ID 5): Logical page size is 2048 bytes.
Checking partition 'sysprocedures_5' (partition ID 5) of table 'sysprocedures'. The logical page size of this table is 2048 bytes.The total number of data pages in partition 'sysprocedures_5' (partition ID 5) is 814.Partition 'sysprocedures_5' (partition ID 5) has 1628 data rows.The total number of empty pages (with all deleted rows) in patition 'sysprocedures_5' (partition ID 5) is 0.The total number of pages in partition 'sysprocedures_5' (partition ID 5) which could be garbage collected to free up some space is 0.The total number of deleted rows in partition 'sysprocedures_5' (partition ID 5) is 0.The total number of pages in partition 'sysprocedures_5' (partition ID 5) with more than 50 percent garbage is 0.The total number of pages in partition 'sysprocedures_5' (partition ID 5) with more than 50 percent insert free space is 0.

The total number of data pages in this table is 814.Table has 1628 data rows.DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.1>


Fixed checkcatalog errors:

CHECKCATALOG ERRORS:dbcc checkcatalog

Table Corrupt: Object id 1296004617 (object name = sp_dbcc_run_summaryreport) does not match between sysprocedures and sysobjectsMsg 2513, Level 16, State 6:Line 1:Table Corrupt: Object id 1312004674 (object name = sp_dbcc_run_runcheck) does not match between sysprocedures and sysobjectsMsg 2513, Level 16, State 6:Line 1:Table Corrupt: Object id 1328004731 (object name = sp_dbcc_run_statisticsreport) does not match between sysprocedures and sysobjects

dropped and recreated the procedures as the sysprocedures table did not have entry for them

1> dbcc checkcatalog2>Checking current database: Logical pagesize is 2048 bytesThe following segments have been defined for database 31515 (database name dbccdb).virtual device number virtual start addr size (logical pages) segments--------------------- -------------------- ---------------------- --------------------4 0 15360 0 15 0 5120 217 0 10240 23 0 10240 0 1DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.1>



Fixed checkalloc errors

Can you advise for the following output from dbcc tablealloc for a particular table dbcc_counters as well as others.
Could there have been some other way to fix this
dbcc tablealloc(dbcc_counters, full, fix)2>00:00000:00012:2008/08/05 17:00:27.66 server Error: 1161, Severity: 21, State: 100:00000:00012:2008/08/05 17:00:27.66 server Invalid OAM entry found at slot 9 in OAM page 1025 for table ID 832002964 with index ID 2 in database 'dbccdb' (ID 31515). This entry is either out of order or its allocation page number is wrong.***************************************************************TABLE: dbcc_counters OBJID = 832002964Msg 2523, Level 16, State 3:Line 1:Table Corrupt: Page number 32500 is out of range for this database dbccdb. The maximum page number in this database is 30719PARTITION ID=832002964 FIRST=1027 ROOT=14777 SORT=0Msg 1161, Level 21, State 1:Line 1:Invalid OAM entry found at slot 9 in OAM page 1025 for table ID 832002964 with index ID 2 in database 'dbccdb' (ID 31515). This entry is either out of order or its allocation page number is wrong.TOTAL # of extents = 0

It looks like page number 32500 is getting associated in dbccdb database while the segmap shows that the last page for dbccdb is ( 20480 + 10240 - 1 ) = 30719

1> select * from master..sysusages where dbid = 315152> dbid segmap lstart size vstart pad unreservedpgs crdate vdevno ------ ----------- ----------- ----------- ----------- ------ ------------- -------------------------- ----------- 31515 3 0 15360 0 NULL 1959 Aug 5 2008 4:55PM 4 31515 4 15360 5120 0 NULL 5100 Aug 5 2008 4:55PM 5 31515 4 20480 10240 0 NULL 10200 Aug 5 2008 4:55PM 17

The OAM page 1025 has following information:

1> dbcc page(31515,1025,1)2>

Page found in Cache: default data cache. Cachelet: 1

BUFFER:Buffer header for buffer 0x20a6ff800 page=0x20a6ff000 bdnew=0x210f15cf8 bdold=0x210f15cf8 bhash=0x0 bmass_next=0x210f2f7e0 bmass_prev=0x2115946b8 bdbid=31515 bvirtpg= [ 0x20a6ff8b8 vpgdevno=4 vpvpn=1025 vdisk=0x203c05c30 ] bmass_head=0x210f15cf8 bmass_tail=0x210f15cf8 bcache_desc=0x20a703650 (cache name='default data cache') bpool_desc=0x20a706dc8 bdbtable=0x202a0e180 Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0 bmass_size=2048 (2K pool) bunref_cnt=0 bmass_stat=0x4001010 (0x04000000 (MASS_DONT_DISCARD), 0x00001000 (MASS_HASHED), 0x00000010 (MASS_INWASH)) bbuf_stat=0x1 (0x00000001 (BUF_PG_REF)) Buffer blpageno=1025 bpg_size=2k Mass blpageno=1025 (Buffer slot #: 0) bxls_pin=0x0 bxls_next=0x0 bspid=0 bxls_flushseq=0 bxls_pinseq=0 bcurrxdes=0x0Latch and the wait queue:Latch (address: 0x20a6ff830) latchmode: 0x0 (FREE_LATCH) latchowner: -1 latchnoofowners: 0 latchwaitq: 0x0 latchwaitqt: 0x0

Latch wait queue:

PAGE HEADER:Page header for page 0x20a6ff000pageno=1025 nextpg=1025 prevpg=1025 ptnid=832002964 timestamp=0000 0003379foampgcount=1 attrcount=10 indid=2 totalentries_lo=12 entrycount=12page status bits: 0x8008 (0x8000 (PG_OAMPG), 0x0008 (PG_OAMATTRIB))

DATA:-----------------------------------------------------------------------------Partition id: 832002964 indid: 2 prevpg: 1025 nextpg: 1025OAM pg cnt: 1 Entry cnt: 12Row count information is not maintained for index pages.Used pgs: 619 Unused pgs: 10Attribute entries: 10OAM status bits set: (0x8000 (PG_OAMPG), 0x0008 (PG_OAMATTRIB))Cannot retrieve OAM attributes as OAM buffer ptr was not provided.OAM pg # 0: 1025 has the following 12 entries (allocpg:used/unused):

[ 0] 768: 8/ 0 1024: 7/ 0 1280: 8/ 0 2560: 56/ 0[ 4] 3584: 32/ 0 3840: 7/ 0 14592: 11/ 5 14848:119/ 0[ 8] 15104:136/ 0 30720: 64/ 0 30976: 56/ 0 32256:115/ 5

OFFSET TABLE:Offset table is not maintained for OAM pages.

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
The 9th slot shows allocation page 30720 for 120 number allocation unit for the database dbccdb

Allocation page 30720 information for object id 832002964 ( dbcc_counters ):

1> dbcc page(31515,30720,1)2>Page 30720 is not in the range of pages for database id 31515DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.1>
Meaning this is not present at all!! But still the OAM page for the dbcc_counters object has its entry. Thus the OAM page for dbcc_counters table in dbccdb database is corrupt

Lets try adding a device to dbccdb database which will allocate more logical pages to the database as few days back we had run disk reinit and refit...

Did a disk init, altered the database to get the following segmap:

1> select * from master..sysusages where dbid = 315152> dbid segmap lstart size vstart pad unreservedpgs crdate vdevno ------ ----------- ----------- ----------- ----------- ------ ------------- -------------------------- ----------- 31515 3 0 15360 0 NULL 1959 Aug 5 2008 4:55PM 4 31515 4 15360 5120 0 NULL 5100 Aug 5 2008 4:55PM 5 31515 4 20480 10240 0 NULL 10200 Aug 5 2008 4:55PM 17 31515 3 30720 10240 0 NULL 10200 Aug 6 2008 11:35AM 3
1> sp_dboption 'dbccdb', 'single', true2>Database option 'single user' turned ON for database 'dbccdb'.Running CHECKPOINT on database 'dbccdb' for option 'single user' to take effect.(return status = 0)1> use dbccdb
1> checkpoint2>
Following errors were getting reported now after addition of the device:

A. TABLE: sysprocedures OBJID = 5PARTITION ID=5 FIRST=169 ROOT=169 SORT=0Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 30720 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [159, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 30976 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [160, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 31232 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [255, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 31488 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [255, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 31744 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [255, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 32000 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [255, 0].Msg 7949, Level 18, State 1:Line 1:The number of pages used and unused for object 5 index 0 partition 5 on allocation page 32256 do not match the counts in the OAM entry. Actual used/unsed counts: [0, 0]. Used/unused counts in OAM entry: [60, 3].There are 3 OAM entries with zero used/unused page counts. Run DBCC TABLEALLOC FIX on objid 5 indid 0.Msg 7940, Level 18, State 1:Line 1:The counts in the OAM are incorrect. This implies that there are entries missing. Run tablealloc utility with the FIX option on the table with the inaccurate OAM counts.
I corrected sysprocedures by running a full, fix:

1> dbcc tablealloc(sysprocedures,full,fix)2>***************************************************************TABLE: sysprocedures OBJID = 5PARTITION ID=5 FIRST=169 ROOT=169 SORT=010 OAM entries with zero used/unused page counts have been deleted for objid 5 indid 0.The oam counts for objid 5 indid 0 are corrected.Data level: indid 0, partition 5. 816 Data pages allocated and 232 Extents allocated.PARTITION ID=5 FIRST=193 ROOT=192 SORT=1Indid : 2, partition : 5. 56 Index pages allocated and 8 Extents allocated.TOTAL # of extents = 240Alloc page 0 (# of extent=3 used pages=18 ref pages=18)Alloc page 768 (# of extent=3 used pages=16 ref pages=16)Alloc page 1280 (# of extent=5 used pages=31 ref pages=31)Alloc page 1536 (# of extent=18 used pages=133 ref pages=133)Alloc page 1792 (# of extent=11 used pages=74 ref pages=74)Alloc page 2048 (# of extent=14 used pages=107 ref pages=107)Alloc page 2304 (# of extent=15 used pages=112 ref pages=112)Alloc page 2560 (# of extent=10 used pages=74 ref pages=74)Alloc page 2816 (# of extent=10 used pages=73 ref pages=73)Alloc page 3072 (# of extent=1 used pages=7 ref pages=7)Alloc page 3328 (# of extent=6 used pages=40 ref pages=40)Alloc page 3584 (# of extent=3 used pages=17 ref pages=17)Alloc page 3840 (# of extent=10 used pages=66 ref pages=66)Alloc page 14592 (# of extent=2 used pages=15 ref pages=15)Alloc page 14848 (# of extent=8 used pages=64 ref pages=64)Alloc page 15104 (# of extent=5 used pages=37 ref pages=37)Total (# of extent=124 used pages=884 ref pages=884) in this databaseDBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.


B.TABLE: dbcc_dev_info OBJID = 784002793PARTITION ID=784002793 FIRST=889 ROOT=889 SORT=0Data level: indid 0, partition 784002793. 1 Data pages allocated and 1 Extents allocated.PARTITION ID=784002793 FIRST=985 ROOT=985 SORT=1Indid : 2, partition : 784002793. 1 Index pages allocated and 1 Extents allocated. Page linkage failed for OBJID 832002964 INDID 0 PARTITION 832002964; Processed 230 pages; Page linkage failed for OBJID 832002964 INDID 2 PARTITION 832002964; Processed 312 pages; Page linkage failed for OBJID 832002964 INDID 2 PARTITION 832002964; Processed 10 pages;TOTAL # of extents = 2

Dropped and re created non clustered index




C.
1> dbcc tablealloc('dbcc_counters',full,fix)2> Page linkage failed for OBJID 832002964 INDID 0 PARTITION 832002964; Processed 230 pages; Page linkage failed for OBJID 832002964 INDID 2 PARTITION 832002964; Processed 312 pages; Page linkage failed for OBJID 832002964 INDID 2 PARTITION 832002964; Processed 10 pages;***************************************************************TABLE: dbcc_counters OBJID = 832002964PARTITION ID=832002964 FIRST=897 ROOT=32500 SORT=1Msg 2529, Level 16, State 7:Line 1:Table Corrupt: Attempted to get page 30760, partition ID 832002964; got page 0, partition ID 0.PARTITION ID=832002964 FIRST=1027 ROOT=14777 SORT=0Msg 2529, Level 16, State 7:Line 1:Table Corrupt: Attempted to get page 30819, partition ID 832002964; got page 0, partition ID 0.Msg 2529, Level 16, State 7:Line 1:Table Corrupt: Attempted to get page 30821, partition ID 832002964; got page 0, partition ID 0.TOTAL # of extents = 0

Truncated the table and ran a full fix:

1> truncate table dbcc_counters
1> dbcc tablealloc(dbcc_counters, full, fix)2>***************************************************************TABLE: dbcc_counters OBJID = 832002964PARTITION ID=832002964 FIRST=897 ROOT=897 SORT=1Data level: indid 0, partition 832002964. 1 Data pages allocated and 1 Extents allocated.PARTITION ID=832002964 FIRST=14777 ROOT=14777 SORT=0Indid : 2, partition : 832002964. 1 Index pages allocated and 2 Extents allocated.TOTAL # of extents = 3Alloc page 768 (# of extent=1 used pages=2 ref pages=2)Alloc page 1024 (# of extent=1 used pages=2 ref pages=2)Alloc page 14592 (# of extent=1 used pages=1 ref pages=1)Total (# of extent=3 used pages=5 ref pages=5) in this databaseDBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

Currently the allocation checks seem to be fine:

1> dbcc listoam(31515,dbcc_counters,0)2>-----------------------------------------------------------------------------Partition id: 832002964 indid: 0 prevpg: 896 nextpg: 896OAM pg cnt: 1 Entry cnt: 1Rows: 0 Rows Per pg: 0Used pgs: 2 Unused pgs: 6Attribute entries: 10OAM status bits set: (0x8000 (PG_OAMPG), 0x0008 (PG_OAMATTRIB), 0x0004 (PG_OAMSORT))LAST SCANNED OAM PAGE: 0ALLOCATION HINTS : 896 0 0 0 0 0 0 0 0 0 0 0 0 0 0OAM pg # 1: 896 has the following 1 entry (allocpg:used/unused):

[ 0] 768: 2/ 6

---- End of OAM chain for partition 832002964 ----DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.1>