syscolumns表报错629，需要重新建立索引

zhuanghp

原帖由 1017of 于 2006-2-17 11:04 发表
dbcc traceon(3604)
go
dbcc page(db1,257)
go

我再试试去，谢谢。

小无赖

有个很简单的方法，把这个用户库备份出来重建之后再load进去不就行了吗。

zhuanghp

sp_role "grant", 'sybase_ts_role',sa
后，执行结果如下：


Page not found in Cache: db1cache.
Read from disk.

BUFFER:
Buffer header for buffer 0x216528000 (Mass head)
    page=0x216528800 bdnew=0x0 bdold=0x0 bhash=0x0
    bmass_next=0x0 bmass_prev=0x0 bvirtpg=83886337 bdbid=4
    bmass_head=0x216528000 bmass_tail=0x216528000 bcache_desc=0x2017a3ee0
    bpool_desc=0x0 bdbtable=0x0
    Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0
    bmass_size=2048 (2K pool) bunref_cnt=0
    bmass_stat=0x0800 (0x00000800 (MASS_NOTHASHED))
    bbuf_stat=0x0 (0x00000000)
    Buffer blpageno=257 bpg_size=2k Mass blpageno=257 (Buffer slot #: 0)
    bxls_pin=0x0 bxls_next=0x0 bspid=0
    bxls_flushseq=0 bxls_pinseq=0 bcurrxdes=0x0
Latch and the wait queue:
Latch (address: 0x216528030)
        latchmode: 0x0 (FREE_LATCH)
        latchowner: 0
        latchnoofowners: 0
        latchwaitq: 0x0 latchwaitqt: 0x0

Latch wait queue:

PAGE HEADER:
Page header for page 0x216528800
pageno=257 nextpg=257 prevpg=257 objid=21 timestamp=0000 000002dc
oampgcount=1 attrcount=10 indid=2 totalentries_lo=1 entrycount=1
page status bits: 0x8008 (0x8000 (PG_OAMPG), 0x0008 (PG_OAMATTRIB))

1017of

dbcc traceon(3604)
go
dbcc tablealloc(db1,3)
go
dbcc tablealloc(db1,21)
go

zhuanghp

好，我再试试，非常感谢。
很想知道第二句的3是指的什么，没看出来，21，我猜是objid=21

1017of

select object_name(3)
go

zhuanghp

原来是syscolumns表呀。

1> dbcc tablealloc (3)
2> go

执行结果：

The default report option of OPTIMIZED is used for this run.
The default fix option of NOFIX is used for this run.
***************************************************************
TABLE: syscolumns               OBJID = 3
INDID=1  FIRST=16        ROOT=40         SORT=0
        Data level: 1.  256 Data pages allocated and 36 Extents allocated.
        Indid     : 1.  3 Index pages allocated and 1 Extents allocated.
TOTAL # of extents = 37
Alloc page 0 (# of extent=1 used pages=4 ref pages=4)
Alloc page 0 (# of extent=1 used pages=8 ref pages=
Alloc page 256 (# of extent=1 used pages=8 ref pages=
Alloc page 768 (# of extent=2 used pages=15 ref pages=15)
Alloc page 1280 (# of extent=3 used pages=24 ref pages=24)
Alloc page 2304 (# of extent=1 used pages=8 ref pages=
Alloc page 658432 (# of extent=1 used pages=7 ref pages=7)
Alloc page 755200 (# of extent=2 used pages=16 ref pages=16)
Alloc page 763648 (# of extent=1 used pages=7 ref pages=7)
Alloc page 1088512 (# of extent=1 used pages=8 ref pages=
Alloc page 4849152 (# of extent=2 used pages=9 ref pages=9)
Alloc page 5653760 (# of extent=1 used pages=7 ref pages=7)
Alloc page 6661888 (# of extent=2 used pages=16 ref pages=16)
Alloc page 7176960 (# of extent=1 used pages=8 ref pages=
Alloc page 7316736 (# of extent=2 used pages=16 ref pages=16)
Alloc page 7380736 (# of extent=2 used pages=16 ref pages=16)
Alloc page 8422656 (# of extent=1 used pages=8 ref pages=
Alloc page 9326080 (# of extent=1 used pages=8 ref pages=
Alloc page 9344000 (# of extent=1 used pages=2 ref pages=2)
Alloc page 9569792 (# of extent=1 used pages=8 ref pages=
Alloc page 10967808 (# of extent=2 used pages=16 ref pages=16)
Alloc page 11206912 (# of extent=1 used pages=8 ref pages=
Alloc page 30158592 (# of extent=1 used pages=8 ref pages=
Alloc page 33155328 (# of extent=1 used pages=8 ref pages=8)
Alloc page 42942976 (# of extent=1 used pages=8 ref pages=8)
Alloc page 49097984 (# of extent=1 used pages=8 ref pages=8)
Alloc page 57883136 (# of extent=1 used pages=8 ref pages=8)
Alloc page 62257664 (# of extent=1 used pages=8 ref pages=8)
Total (# of extent=37 used pages=275 ref pages=275) in this database

Statistical information for this run follows:
Total # of pages read = 261
Total # of pages found cache = 127
Total # of physical reads = 108
Total # of saved I/O = 26
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

做：
1>dbcc tablealloc(3.full.fix)
2>go
等了很久，没结果呀，错了吗？
或者要等很长时间？

1017of

dbcc indexalloc(3,1)

zhuanghp

谢谢1017of 一次一次指导，我继续试。

1> dbcc indexalloc(3,1)
2> go

The default report option of OPTIMIZED is used for this run.
The default fix option of NOFIX is used for this run.
***************************************************************
TABLE: syscolumns               OBJID = 3
INDID=1  FIRST=16        ROOT=40         SORT=0
        Data level: 1.  256 Data pages allocated and 36 Extents allocated.
        Indid     : 1.  3 Index pages allocated and 1 Extents allocated.
TOTAL # of extents = 37
Alloc page 0 (# of extent=1 used pages=4 ref pages=4)
Alloc page 0 (# of extent=1 used pages=8 ref pages=
Alloc page 256 (# of extent=1 used pages=8 ref pages=
Alloc page 768 (# of extent=2 used pages=15 ref pages=15)
Alloc page 1280 (# of extent=3 used pages=24 ref pages=24)
Alloc page 2304 (# of extent=1 used pages=8 ref pages=
Alloc page 658432 (# of extent=1 used pages=7 ref pages=7)
Alloc page 755200 (# of extent=2 used pages=16 ref pages=16)
Alloc page 763648 (# of extent=1 used pages=7 ref pages=7)
Alloc page 1088512 (# of extent=1 used pages=8 ref pages=
Alloc page 4849152 (# of extent=2 used pages=9 ref pages=9)
Alloc page 5653760 (# of extent=1 used pages=7 ref pages=7)
Alloc page 6661888 (# of extent=2 used pages=16 ref pages=16)
Alloc page 7176960 (# of extent=1 used pages=8 ref pages=
Alloc page 7316736 (# of extent=2 used pages=16 ref pages=16)
Alloc page 7380736 (# of extent=2 used pages=16 ref pages=16)
Alloc page 8422656 (# of extent=1 used pages=8 ref pages=
Alloc page 9326080 (# of extent=1 used pages=8 ref pages=
Alloc page 9344000 (# of extent=1 used pages=2 ref pages=2)
Alloc page 9569792 (# of extent=1 used pages=8 ref pages=
Alloc page 10967808 (# of extent=2 used pages=16 ref pages=16)
Alloc page 11206912 (# of extent=1 used pages=8 ref pages=
Alloc page 30158592 (# of extent=1 used pages=8 ref pages=
Alloc page 33155328 (# of extent=1 used pages=8 ref pages=8)
Alloc page 42942976 (# of extent=1 used pages=8 ref pages=8)
Alloc page 49097984 (# of extent=1 used pages=8 ref pages=8)
Alloc page 57883136 (# of extent=1 used pages=8 ref pages=8)
Alloc page 62257664 (# of extent=1 used pages=8 ref pages=8)
Total (# of extent=37 used pages=275 ref pages=275) in this database

Statistical information for this run follows:
Total # of pages read = 261
Total # of pages found cache = 122
Total # of physical reads = 115
Total # of saved I/O = 24
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.


另外：
1> dbcc tablealloc(21)
2> go
The default report option of OPTIMIZED is used for this run.
The default fix option of NOFIX is used for this run.
***************************************************************
TABLE: sysattributes            OBJID = 21
INDID=1  FIRST=281       ROOT=273        SORT=1
        Data level: 1.  1 Data pages allocated and 1 Extents allocated.
        Indid     : 1.  1 Index pages allocated and 1 Extents allocated.
INDID=2  FIRST=258       ROOT=258        SORT=0
        Indid     : 2.  1 Index pages allocated and 1 Extents allocated.
TOTAL # of extents = 3
Alloc page 256 (# of extent=1 used pages=2 ref pages=2)
Alloc page 256 (# of extent=1 used pages=2 ref pages=2)
Alloc page 256 (# of extent=1 used pages=3 ref pages=3)
Total (# of extent=3 used pages=7 ref pages=7) in this database

Statistical information for this run follows:
Total # of pages read = 6
Total # of pages found cache = 6
Total # of physical reads = 0
Total # of saved I/O = 0
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

1>
2> dbcc indexalloc(21,1)
3> go
The default report option of OPTIMIZED is used for this run.
The default fix option of NOFIX is used for this run.
***************************************************************
TABLE: sysattributes            OBJID = 21
INDID=1  FIRST=281       ROOT=273        SORT=1
        Data level: 1.  1 Data pages allocated and 1 Extents allocated.
        Indid     : 1.  1 Index pages allocated and 1 Extents allocated.
TOTAL # of extents = 2
Alloc page 256 (# of extent=1 used pages=2 ref pages=2)
Alloc page 256 (# of extent=1 used pages=2 ref pages=2)
Total (# of extent=2 used pages=4 ref pages=4) in this database

Statistical information for this run follows:
Total # of pages read = 4
Total # of pages found cache = 4
Total # of physical reads = 0
Total # of saved I/O = 0
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

1> dbcc indexalloc(21,2)
2> go
The default report option of OPTIMIZED is used for this run.
The default fix option of NOFIX is used for this run.
***************************************************************
TABLE: sysattributes            OBJID = 21
INDID=2  FIRST=258       ROOT=258        SORT=0
        Indid     : 2.  1 Index pages allocated and 1 Extents allocated.
TOTAL # of extents = 1
Alloc page 256 (# of extent=1 used pages=3 ref pages=3)
Total (# of extent=1 used pages=3 ref pages=3) in this database

Statistical information for this run follows:
Total # of pages read = 2
Total # of pages found cache = 2
Total # of physical reads = 0
Total # of saved I/O = 0
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

再帮我看看，629错误还在继续。

1017of

if you have much time to confirm what is the situation of your user database,I suggest you do full-database check(command: dbcc checkalloc(db1) & dbcc checkdb(db1)).the time dbcc checkalloc & dbcc checkdb would be in accord with your user data.more data exists,more time is needed.you must not do any transactions in terms of dbcc.
if time is luxury to you,you could check as dbcc tablealloc(3,FULL) & dbcc indexalloc(3,1,FULL). the same check should be did on object 21.if no more error information appear,full-database check is the only option left.
ps:since dbcc tablealloc & dbcc indexalloc have no obvious dbid or dbname parameter,you should make sure check your objects on the database you want.good luck.