sqlserver:dbcc page
发布时间：2020-12-12 14:37:01 所属栏目：MsSql教程 来源：网络整理
导读：转载自“老侯的菜园子” 博客： http://www.voidcn.com/article/p-exukmyud-kn.html ---------------------------- --dbcc page内容解释 --author:boyi55 ---------------------------- ========================= dbcc traceon(3064) dbcc page(northwind,1
|
转载自“老侯的菜园子” 博客： http://www.voidcn.com/article/p-exukmyud-kn.html dbcc page(northwind,1,100,1) go ========================= 数据文件的页面结构（管理页面 比较重要） 0 文件头页面 1 PFS页面 2 GAM页面 3 SGAM页面 6 DCM页面 7 BCM页面 一个GAM和一个SGAM可以管理4GB的数据空间 ========================== DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 PAGE: (1:100) ------------- BUFFER: ------- 内存中页面的管理信息 BUF @0x19217B80-- 内存中页号 --------------- bpage = 0x19A3C000（ 对应物理页面）? bhash = 0x00000000（hash） bpageno = (1:100)（ 对应物理文件的页面号） bdbid = 6 ( 对应的数据库ID）?????????????? breferences = 0?????????? bstat = 0x9 bspin = 0????????????????????????? bnext = 0x00000000??????? PAGE HEADER: ------------ 页头：96字节 Page @0x19A3C000 ---------------- m_pageId = (1:100)（ 页号）?? m_headerVersion = 1?????????????????????? m_type = 1 m_typeFlagBits = 0x0???????? m_level = 0 （ 索引级别）????????????????? m_flagBits = 0x8000 m_objId = 2041058307（ 对应表ID）? m_indexId = 0??????????????????????? m_prevPage = (0:0) m_nextPage = (0:0)?????????? pminlen = 8?????????????????????????????? m_slotCnt = 8（ 页面中数据的行数，八行） m_freeCnt = 7504 （ 空余空间）? m_freeData = 672 （ 空余空间偏移量，已用空间数）?????? m_reservedCnt = 0 m_lsn = (4:270:16)?????????? m_xactReserved = 0??????????????????????? m_xdesId = (0:0) m_ghostRecCnt = 0??????????? m_tornBits = 1??????????? Allocation Status ----------------- GAM (1:2) = ALLOCATED （ 管理盘区位于第三个页面编号2）??? SGAM (1:3) = ALLOCATED（ 管理混合盘区位于第四个页面编号是三?） PFS (1:1) = 0x60 MIXED_EXT ALLOCATED?? 0_PCT_FULL?? DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED DATA: ----- Slot 0,Offset 0x60 ------------------- Record Type = PRIMARY_RECORD??????????????????????? Record Attributes =? NULL_BITMAP VARIABLE_COLUMNS?? 19a3c060:? 00080030? 00000001? 03000004? 35002500 0............%.5 19a3c070:? 42804580? 76006500? 72006500? 67006100 .E.B.e.v.e.r.a.g 19a3c080:? 73006500? 87000000? 00000000? 00005f00 .e.s........._.. 19a3c090:? 01000100? 88000000? 00000000? 00005f00 ............._.. 19a3c0a0:? 03000100??????? 00???????????????????? ..... Slot 1,Offset 0xa5 ------------------- Record Type = PRIMARY_RECORD??????????????????????? Record Attributes =? NULL_BITMAP VARIABLE_COLUMNS?? 19a3c0a5:? 00080030? 00000002? 03000004? 37002700 0............'.7 19a3c0b5:? 43804780? 6e006f00? 69006400? 65006d00 .G.C.o.n.d.i.m.e 19a3c0c5:? 74006e00? 00007300? 00008900? 5f000000 .n.t.s........._ 19a3c0d5:? 01000000? 00000500? 00008a00? 5f000000 ..............._ 19a3c0e5:? 01000000??? 000700???????????????????? ....... Slot 2,Offset 0xec ------------------- Record Type = PRIMARY_RECORD??????????????????????? Record Attributes =? NULL_BITMAP VARIABLE_COLUMNS?? 19a3c0ec:? 00080030? 00000003? 03000004? 39002900 0............).9 19a3c0fc:? 43804980? 6e006f00? 65006600? 74006300 .I.C.o.n.f.e.c.t 19a3c10c:? 6f006900? 73006e00? 8b000000? 00000000 .i.o.n.s........ 19a3c11c:? 00005f00? 09000100? 8c000000? 00000000 ._.............. 19a3c12c:? 00006900? 01000100??????? 00?????????? .i....... Slot 3,Offset 0x135 -------------------- Record Type = PRIMARY_RECORD??????????????????????? Record Attributes =? NULL_BITMAP VARIABLE_COLUMNS?? 19a3c135:? 00080030? 00000004? 03000004? 3f002f00 0............/.? 19a3c145:? 44804f80? 69006100? 79007200? 50002000 .O.D.a.i.r.y. .P 19a3c155:? 6f007200? 75006400? 74006300? 00007300 .r.o.d.u.c.t.s.. 19a3c165:? 00008d00? 5f000000? 01000000? 00000a00 ......._........ 19a3c175:? 00008e00? 5f000000? 01000000??? 000b00 ......._....... Slot 4,Offset 0x184 -------------------- Record Type = PRIMARY_RECORD??????????????????????? Record Attributes =? NULL_BITMAP VARIABLE_COLUMNS?? 19a3c184:? 00080030? 00000005? 03000004? 3f002f00 0............/.? 19a3c194:? 47804f80? 61007200? 6e006900? 2f007300 .O.G.r.a.i.n.s./ 19a3c1a4:? 65004300? 65007200? 6c006100? 00007300 .C.e.r.e.a.l.s.. 19a3c1b4:? 00008f00? 5f000000? 01000000? 00000c00 ......._........ 19a3c1c4:? 00009000? 6c000000? 01000000??? 000100 .......l....... Slot 5,Offset 0x1d3 -------------------- Record Type = PRIMARY_RECORD??????????????????????? Record Attributes =? NULL_BITMAP VARIABLE_COLUMNS?? 19a3c1d3:? 00080030? 00000006? 03000004? 3b002b00 0............+.; 19a3c1e3:? 4d804b80? 61006500? 2f007400? 6f005000 .K.M.e.a.t./.P.o 19a3c1f3:? 6c007500? 72007400? 00007900? 00009100 .u.l.t.r.y...... 19a3c203:? 69000000? 01000000? 00000400? 00009200 ...i............ 19a3c213:? 69000000? 01000000??? 000500?????????? ...i....... Slot 6,Offset 0x21e -------------------- Record Type = PRIMARY_RECORD??????????????????????? Record Attributes =? NULL_BITMAP VARIABLE_COLUMNS?? 19a3c21e:? 00080030? 00000007? 03000004? 31002100 0............!.1 19a3c22e:? 50804180? 6f007200? 75006400? 65006300 .A.P.r.o.d.u.c.e 19a3c23e:? 93000000? 00000000? 00006900? 06000100 .........i...... 19a3c24e:? 94000000? 00000000? 00007200? 01000100 .........r...... 19a3c25e:??????? 00?????????????????????????????? . Slot 7,Offset 0x25f -------------------- Record Type = PRIMARY_RECORD??????????????????????? Record Attributes =? NULL_BITMAP VARIABLE_COLUMNS?? 19a3c25f:? 00080030? 00000008? 03000004? 31002100 0............!.1 19a3c26f:? 53804180? 61006500? 6f006600? 64006f00 .A.S.e.a.f.o.o.d 19a3c27f:? 95000000? 00000000? 00006900? 07000100 .........i...... 19a3c28f:? 96000000? 00000000? 00006900? 08000100 .........i...... 19a3c29f:??????? 00?????????????????????????????? . OFFSET TABLE: ------------- Row - Offset?? 96 （页头）+（672-96）（数据空间）+7504（空余空间）+16（行偏移指针数组）=8192（页面大小） 8190-8191 slot0 ... ...?? 行偏移数组 8176-8177 slot7 672-8175 空余空间????????? 7 (0x7) - 607 (0x25f) 607-671??? 6 (0x6) - 542 (0x21e) 542-606??? 5 (0x5) - 467 (0x1d3) 467-541??? 4 (0x4) - 388 (0x184) 388-466??? 3 (0x3) - 309 (0x135) 309-387??? 2 (0x2) - 236 (0xec)? 236-308 1 (0x1) - 165 (0xa5)? 165-235??? 0 (0x0) - 96 (0x60)?? 96-164 0-95 pageheader DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 ============================================ create table demodb1..table10( id int not null, name char(5) not null, address char(10) null) select * from demodb1..sysindexes where id=object_id('demodb1..table10') ============================================ id????????? status????? first????????? indid? root?????????? minlen keycnt groupid dpages????? reserved??? used??????? rowcnt?????????????? rowmodctr?? reserved3 reserved4 xmaxlen maxirow OrigFillFactor StatVersion reserved2?? FirstIAM?????? impid? lockflags pgmodctr??? keys?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? name???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? statblob???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? maxlen????? rows??????? ----------- ----------- -------------- ------ -------------- ------ 1977058079? 0?????????? 0x000000000000 0????? 0x000000000000 23???? 0????? 1?????? 0?????????? 0?????????? 0?????????? 0??????????????????? 0?????????? 0???????? 0???????? 40????? 0?????? 0????????????? 0?????????? 0?????????? 0x000000000000 -1???? 0???????? 0?????????? NULL?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? table10????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? NULL???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? 8000??????? 0 无数据 first为指向第一列或者根页的指针。 ============================================= insert into demodb1..table10(id,name,address)values(1,'abcde','suzhou') ================================= id????????? status????? first????????? indid? root?????????? minlen keycnt groupid dpages????? reserved??? used??????? rowcnt?????????????? rowmodctr?? reserved3 reserved4 xmaxlen maxirow OrigFillFactor StatVersion reserved2?? FirstIAM?????? impid? lockflags pgmodctr??? keys?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? name???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? statblob???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? maxlen????? rows??????? ----------- ----------- -------------- ------ -------------- ------ ------? 1977058079? 0?????????? 0x0F0000000100 0????? 0x0F0000000100 23???? 0????? 1?????? 1?????????? 2?????????? 2?????????? 1??????????????????? 1?????????? 0???????? 0???????? 40????? 0?????? 0????????????? 0?????????? 0?????????? 0x190000000100 -1???? 0???????? 0?????????? NULL?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? table10????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? NULL???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? 8000??????? 1 first 0x0F 编号十五页 ================================= create table table11(id int,name text) select * from demodb1..sysindexes where id=object_id('demodb1..table11') insert into demodb1..table11 values(1,'boyi55') ================================= id????????? status????? first????????? indid? root?????????? minlen keycnt groupid dpages????? reserved??? used??????? rowcnt?????????????? rowmodctr?? reserved3 reserved4 xmaxlen maxirow OrigFillFactor StatVersion reserved2?? FirstIAM?????? impid? lockflags pgmodctr??? keys?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? name???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? statblob???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? maxlen????? rows??????? ----------- ----------- -------------- ------ -------------- ------? 1993058136? 0?????????? 0x1E0000000100 0????? 0x1E0000000100 8????? 0????? 1?????? 1?????????? 2?????????? 2?????????? 1??????????????????? 1?????????? 0???????? 0???????? 43????? 0?????? 0????????????? 0?????????? 0?????????? 0x1F0000000100 -1???? 0???????? 0?????????? NULL?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? table11????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? NULL???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? 8000??????? 1 1993058136? 2?????????? 0x1C0000000100 255??? 0x1C0000000100 0????? 0????? 1?????? 0?????????? 2?????????? 2?????????? 0??????????????????? 0?????????? 0???????? 0???????? 0?????? 0?????? 0????????????? 0?????????? 0?????????? 0x1D0000000100 -1???? 0???????? 0?????????? NULL?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ttable11???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? NULL???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? 8000??????? 0 first 0x1E 编号三十页 first 0x1C 编号二十八页 ===================== DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 PAGE: (1:30) ------------ BUFFER: ------- BUF @0x192192C0 --------------- bpage = 0x19AF6000??????? bhash = 0x00000000??????? bpageno = (1:30) bdbid = 7???????????????? breferences = 1?????????? bstat = 0xb bspin = 0???????????????? bnext = 0x00000000??????? PAGE HEADER: ------------ Page @0x19AF6000 ---------------- m_pageId = (1:30)???????? m_headerVersion = 1?????? m_type = 1 m_typeFlagBits = 0x0????? m_level = 0?????????????? m_flagBits = 0x8000 m_objId = 1993058136????? m_indexId = 0???????????? m_prevPage = (0:0) m_nextPage = (0:0)??????? pminlen = 8?????????????? m_slotCnt = 1 m_freeCnt = 8063????????? m_freeData = 127????????? m_reservedCnt = 0 m_lsn = (5:49:1)????????? m_xactReserved = 0??????? m_xdesId = (0:0) m_ghostRecCnt = 0???????? m_tornBits = 0??????????? Allocation Status ----------------- GAM (1:2) = ALLOCATED???? SGAM (1:3) = ALLOCATED??? PFS (1:1) = 0x61 MIXED_EXT ALLOCATED? 50_PCT_FULL?? DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED Slot 0 Offset 0x60 ------------------ Record Type = PRIMARY_RECORD??????????????????????? Record Attributes =? NULL_BITMAP VARIABLE_COLUMNS?? 19af6060:? 00080030? 00000001? 01000002? 00801f00 0............... 19af6070:? 0000c900? 1c000000? 01000000??? 000000 ............... id?????????????????????????????? = 1??????????????? name???????????????????????????? = [TextPointer] ------------------------------------------------ TextTimeStamp = 13172736? RowId = (1:28:0) 指针指向页号28. ???????? DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 ============================== sp_tableoption table11,'text in row',1000 update table11 set name='boyi55555' where id =1 ? DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 PAGE: (1:30) ------------ BUFFER: ------- BUF @0x192192C0 --------------- bpage = 0x19AF6000??????? bhash = 0x00000000??????? bpageno = (1:30) bdbid = 7???????????????? breferences = 4?????????? bstat = 0xb bspin = 0???????????????? bnext = 0x00000000??????? PAGE HEADER: ------------ Page @0x19AF6000 ---------------- m_pageId = (1:30)???????? m_headerVersion = 1?????? m_type = 1 m_typeFlagBits = 0x0????? m_level = 0?????????????? m_flagBits = 0x8000 m_objId = 1993058136????? m_indexId = 0???????????? m_prevPage = (0:0) m_nextPage = (0:0)??????? pminlen = 8?????????????? m_slotCnt = 1 m_freeCnt = 8070????????? m_freeData = 127????????? m_reservedCnt = 7 m_lsn = (5:52:5)????????? m_xactReserved = 7??????? m_xdesId = (0:188) m_ghostRecCnt = 0???????? m_tornBits = 0??????????? Allocation Status ----------------- GAM (1:2) = ALLOCATED???? SGAM (1:3) = ALLOCATED??? PFS (1:1) = 0x61 MIXED_EXT ALLOCATED? 50_PCT_FULL?? DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED Slot 0 Offset 0x60 ------------------ Record Type = PRIMARY_RECORD??????????????????????? Record Attributes =? NULL_BITMAP VARIABLE_COLUMNS?? 19af6060:? 00080030? 00000001? 01000002? 62001800 0..............b 19af6070:? 3569796f? 35353535???????????????????? oyi55555 id?????????????????????????????? = 1??????????????? ******************************************************** name???????????????????????????? = [BLOB Inline Data] ----------------------------------------------------- 19af606f:? 69796f62? 35353535??????? 35?????????? boyi55555 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 ============================================= DBCC EXTENTINFO 得到属于一个对象的所有盘区的列表 dbcc traceon(3604) dbcc extentinfo(northwind,categories) DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 file_id???? page_id???? pg_alloc??? ext_size??? obj_id????? index_id??? pfs_bytes????????? ----------- ----------- ----------- ----------- ----------- ----------- ------------------ 1?????????? 98????????? 1?????????? 1?????????? 2041058307? 1?????????? 0x6000000000000000 1?????????? 100???????? 1?????????? 1?????????? 2041058307? 1?????????? 0x6000000000000000 1?????????? 389???????? 1?????????? 1?????????? 2041058307? 2?????????? 0x6000000000000000 1?????????? 95????????? 1?????????? 1?????????? 2041058307? 255???????? 0x6200000000000000 1?????????? 97????????? 1?????????? 1?????????? 2041058307? 255???????? 0x6400000000000000 1?????????? 103???????? 1?????????? 1?????????? 2041058307? 255???????? 0x6400000000000000 1?????????? 104???????? 1?????????? 1?????????? 2041058307? 255???????? 0x6400000000000000 1?????????? 105???????? 1?????????? 1?????????? 2041058307? 255???????? 0x6200000000000000 1?????????? 106???????? 1?????????? 1?????????? 2041058307? 255???????? 0x6400000000000000 1?????????? 107???????? 1?????????? 1?????????? 2041058307? 255???????? 0x6400000000000000 1?????????? 108???????? 1?????????? 1?????????? 2041058307? 255???????? 0x6200000000000000 1?????????? 112???????? 4?????????? 8?????????? 2041058307? 255???????? 0x4444424400000000 （所影响的行数为 12 行） DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 page_id 对应于obj_id dbcc traceon(3604) dbcc extentinfo(northwind) 用法：DBCC EXTENTINFO(dbname,tablename,indexid) （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |