DBCC PAGE verwenden
By Frank Kalis
Durch den DBCC PAGE Befehl kann man sich den Inhalt jeder Daten- und Indexseite in SQL Server ansehen. Die Verwendung des Befehls ist aber mit einigen Stolperfallen verbunden, die im folgenden erklärt werden sollen. Beispielhaft verwenden wir mal die PUBS Datenbank.
USE PUBS GO
Als nächstes brauchen wir eine Variable, die den Wert der Spalte first in der Systemtabelle sysindexes aufnimmt. Achtung!: Laut "Inside SQL Server 2000" ist nicht sichergestellt, daß die Spalte first stets den Wert zur ersten Datenseite enthält. Speziell dann nicht, wenn mit den Daten der Tabellen gearbeitet wird; also Lösch-, Änderungs- und Einfügeoperationen stattgefunden haben.
DECLARE @page_adress AS BINARY(6)
BINARY(6) deshalb, da 2 Bytes die Informationen zur Datei enthalten, die anderen 4 Bytes die Informationen zur Seite. Die Abfrage auf die sysindexes Tabelle sieht dann folgendemaßen aus:
SELECT
@page_adress = [first]
FROM
sysindexes
WHERE
[id] = OBJECT_ID('authors')
AND
indid=1
Als Ergebnis kommt bei mir der Wert 0xC50000000100 zurück. Hieraus muß man nun Rückschlüsse ziehen, welche Datei und welche Seite gemeint sein könnten. So, und jetzt wird's etwas tricky. Man muß wissen, daß die Werte von SQL Server nicht in der "korrekten" Reihenfolge gespeichert werden, sondern in einem "gespiegelten" Format. Zur Dekodierung kann man etwas selbstgemachtes einsetzen oder dieses Skriupt von SQL Server MVP Itzik Ben-Gan verwenden, welches hier mit seiner Erlaubnis wiedergegeben gibt:
SELECT CAST ( SUBSTRING(@page_adress, 6, 1) + SUBSTRING(@page_adress, 5, 1) AS INT ) AS file# , CAST ( SUBSTRING(@page_adress, 4, 1) + SUBSTRING(@page_adress, 3, 1) + SUBSTRING(@page_adress, 2, 1) + SUBSTRING(@page_adress, 1, 1) AS INT ) AS page# file# page# ----------- ----------- 1 197
Das Ergebnis der Abfrage kann man nun sehr einfach in den DBCC PAGE Befehl einbauen. Dabei ist es wichtig, das als erstes die Ausgabe von DBCC PAGE auf den Client umgelenkt wird. Dies geschieht mit
DBCC TRACEON(3604)
Jetzt kann der eigentliche DBCC PAGE Befehl ausgeführt werden:
DBCC PAGE('pubs',1,197,3)
DBCC-Ausführung abgeschlossen. Falls DBCC Fehlermeldungen ...
PAGE: (1:197)
-------------
BUFFER:
-------
BUF @0x00E18B00
---------------
bpage = 0x1A018000 bhash = 0x00000000 bpageno = (1:197)
bdbid = 5 breferences = 1 bstat = 0x9
bspin = 0 bnext = 0x00000000
PAGE HEADER:
------------
Page @0x1A018000
----------------
m_pageId = (1:197) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 1977058079 m_indexId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 24 m_slotCnt = 23
m_freeCnt = 6004 m_freeData = 2230 m_reservedCnt = 0
m_lsn = (15:34:5) m_xactReserved = 0 m_xdesId = (0:554)
m_ghostRecCnt = 0 m_tornBits = 26
Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x858
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A018858: 00180030 20383034 2d363934 33323237 0...408 496-7223
1A018868: 34394143 01353230 00000009 00330005 CA94025.......3.
1A018878: 0045003e 005e0054 2d323731 312d3233 >.E.T.^.172-32-1
1A018888: 48363731 6f6c6c65 726f5720 6f4a646c 176Hello WorldJo
1A018898: 6f736e68 3930316e 42203233 65676769 hnson10932 Bigge
1A0188A8: 2e645220 6c6e654d 6150206f 6b72 Rd.Menlo Park
au_id = 172-32-1176
au_lname = Hello World
au_fname = Johnson
phone = 408 496-7223
address = 10932 Bigge Rd.
city = Menlo Park
state = CA
zip = 94025
contract = 1
Slot 1 Offset 0xb8
------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A0180B8: 00180030 20353134 2d363839 30323037 0...415 986-7020
1A0180C8: 34394143 01383136 00000009 00330005 CA94618.......3.
1A0180D8: 00400038 00580051 2d333132 382d3634 8.@.Q.X.213-46-8
1A0180E8: 47353139 6e656572 6a72614d 6569726f 915GreenMarjorie
1A0180F8: 20393033 64723336 2e745320 31342320 309 63rd St. #41
1A018108: 6b614f31 646e616c 1Oakland
au_id = 213-46-8915
au_lname = Green
au_fname = Marjorie
phone = 415 986-7020
address = 309 63rd St. #411
city = Oakland
state = CA
zip = 94618
contract = 1
Slot 2 Offset 0x110
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A018110: 00180030 20353134 2d383435 33323737 0...415 548-7723
1A018120: 34394143 01353037 00000009 00330005 CA94705.......3.
1A018130: 003f0039 0055004d 2d383332 372d3539 9.?.M.U.238-95-7
1A018140: 43363637 6f737261 6568436e 356c7972 766CarsonCheryl5
1A018150: 44203938 69777261 6e4c206e 7265422e 89 Darwin Ln.Ber
1A018160: 656c656b 79 keley
au_id = 238-95-7766
au_lname = Carson
au_fname = Cheryl
phone = 415 548-7723
address = 589 Darwin Ln.
city = Berkeley
state = CA
zip = 94705
contract = 1
Slot 3 Offset 0x165
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A018165: 00180030 20383034 2d363832 38323432 0...408 286-2428
1A018175: 35394143 01383231 00000009 00330005 CA95128.......3.
1A018185: 0041003a 005d0055 2d373632 322d3134 :.A.U.].267-41-2
1A018195: 4f343933 61654c27 694d7972 65616863 394O'LearyMichae
1A0181A5: 2032326c 76656c43 6e616c65 76412064 l22 Cleveland Av
1A0181B5: 3123202e 6e615334 736f4a20 65 . #14San Jose
au_id = 267-41-2394
au_lname = O'Leary
au_fname = Michael
phone = 408 286-2428
address = 22 Cleveland Av. #14
city = San Jose
state = CA
zip = 95128
contract = 1
Slot 4 Offset 0x1c2
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A0181C2: 00180030 20353134 2d343338 39313932 0...415 834-2919
1A0181D2: 34394143 01393036 00000009 00330005 CA94609.......3.
1A0181E2: 003f003b 0056004f 2d343732 392d3038 ;.?.O.V.274-80-9
1A0181F2: 53313933 69617274 44746867 356e6165 391StraightDean5
1A018202: 20303234 6c6c6f43 20656765 4f2e7641 420 College Av.O
1A018212: 616c6b61 646e akland
au_id = 274-80-9391
au_lname = Straight
au_fname = Dean
phone = 415 834-2919
address = 5420 College Av.
city = Oakland
state = CA
zip = 94609
contract = 1
Slot 5 Offset 0x218
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A018218: 00180030 20333139 2d333438 32363430 0...913 843-0462
1A018228: 3636534b 00343430 00000009 00330005 KS66044.......3.
1A018238: 003f0038 00590051 2d313433 312d3232 8.?.Q.Y.341-22-1
1A018248: 53323837 6874696d 6e61654d 31726564 782SmithMeander1
1A018258: 694d2030 73697373 70706973 72442069 0 Mississippi Dr
1A018268: 77614c2e 636e6572 65 .Lawrence
au_id = 341-22-1782
au_lname = Smith
au_fname = Meander
phone = 913 843-0462
address = 10 Mississippi Dr.
city = Lawrence
state = KS
zip = 66044
contract = 0
Slot 6 Offset 0x271
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A018271: 00180030 20353134 2d383536 32333939 0...415 658-9932
1A018281: 34394143 01353037 00000009 00330005 CA94705.......3.
1A018291: 00400039 00580050 2d393034 372d3635 9.@.P.X.409-56-7
1A0182A1: 42383030 656e6e65 72624174 6d616861 008BennetAbraham
1A0182B1: 33323236 74614220 6e616d65 2e745320 6223 Bateman St.
1A0182C1: 6b726542 79656c65 Berkeley
au_id = 409-56-7008
au_lname = Bennet
au_fname = Abraham
phone = 415 658-9932
address = 6223 Bateman St.
city = Berkeley
state = CA
zip = 94705
contract = 1
Slot 7 Offset 0x2c9
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A0182C9: 00180030 20353134 2d363338 38323137 0...415 836-7128
1A0182D9: 34394143 01313033 00000009 00330005 CA94301.......3.
1A0182E9: 003a0037 00520049 2d373234 322d3731 7.:.I.R.427-17-2
1A0182F9: 44393133 416c6c75 34336e6e 42203031 319DullAnn3410 B
1A018309: 646e6f6c 74532065 6c61502e 6c41206f londe St.Palo Al
1A018319: 6f74 to
au_id = 427-17-2319
au_lname = Dull
au_fname = Ann
phone = 415 836-7128
address = 3410 Blonde St.
city = Palo Alto
state = CA
zip = 94301
contract = 1
Slot 8 Offset 0x31b
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A01831B: 00180030 20373037 2d383339 35343436 0...707 938-6445
1A01832B: 35394143 01383234 00000009 00330005 CA95428.......3.
1A01833B: 0041003d 0051004b 2d323734 322d3732 =.A.K.Q.472-27-2
1A01834B: 47393433 676e6972 6273656c 72754279 349GringlesbyBur
1A01835B: 204f5074 20786f42 43323937 6c65766f tPO Box 792Covel
1A01836B: 6f o
au_id = 472-27-2349
au_lname = Gringlesby
au_fname = Burt
phone = 707 938-6445
address = PO Box 792
city = Covelo
state = CA
zip = 95428
contract = 1
Slot 9 Offset 0x36c
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A01836C: 00180030 20353134 2d353835 30323634 0...415 585-4620
1A01837C: 34394143 01303331 00000009 00330005 CA94130.......3.
1A01838C: 0043003b 005f0052 2d363834 312d3932 ;.C.R._.486-29-1
1A01839C: 4c363837 736b636f 4379656c 6c726168 786LocksleyCharl
1A0183AC: 31656e65 72422038 7764616f 41207961 ene18 Broadway A
1A0183BC: 61532e76 7246206e 69636e61 6f6373 v.San Francisco
au_id = 486-29-1786
au_lname = Locksley
au_fname = Charlene
phone = 415 585-4620
address = 18 Broadway Av.
city = San Francisco
state = CA
zip = 94130
contract = 1
Slot 10 Offset 0x3cb
--------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A0183CB: 00180030 20353136 2d373932 33323732 0...615 297-2723
1A0183DB: 37334e54 00353132 00000009 00330005 TN37215.......3.
1A0183EB: 00440039 00610058 2d373235 332d3237 9.D.X.a.527-72-3
1A0183FB: 47363432 6e656572 726f4d65 676e696e 246GreeneMorning
1A01840B: 72617473 47203232 62796172 48207261 star22 Graybar H
1A01841B: 6573756f 2e645220 6873614e 6c6c6976 ouse Rd.Nashvill
1A01842B: 65 e
au_id = 527-72-3246
au_lname = Greene
au_fname = Morningstar
phone = 615 297-2723
address = 22 Graybar House Rd.
city = Nashville
state = TN
zip = 37215
contract = 0
Slot 11 Offset 0x42c
--------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A01842C: 00180030 20333035 2d353437 32303436 0...503 745-6402
1A01843C: 3739524f 01303333 00000009 00330005 OR97330.......3.
1A01844C: 00490041 00620059 2d383436 312d3239 A.I.Y.b.648-92-1
1A01845C: 42323738 63746f6c 2d746568 6c6c6148 872Blotchet-Hall
1A01846C: 67655273 6c616e69 20353564 6c6c6948 sReginald55 Hill
1A01847C: 6c616473 6c422065 726f432e 6c6c6176 sdale Bl.Corvall
1A01848C: 7369 is
au_id = 648-92-1872
au_lname = Blotchet-Halls
au_fname = Reginald
phone = 503 745-6402
address = 55 Hillsdale Bl.
city = Corvallis
state = OR
zip = 97330
contract = 1
Slot 12 Offset 0x48e
--------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A01848E: 00180030 20353134 2d353339 38323234 0...415 935-4228
1A01849E: 34394143 01353935 00000009 00330005 CA94595.......3.
1A0184AE: 0040003b 0058004c 2d323736 332d3137 ;.@.L.X.672-71-3
1A0184BE: 59393432 6d6f6b6f 416f746f 6f6b696b 249YokomotoAkiko
1A0184CE: 69532033 7265766c 2e744320 6e6c6157 3 Silver Ct.Waln
1A0184DE: 43207475 6b656572 ut Creek
au_id = 672-71-3249
au_lname = Yokomoto
au_fname = Akiko
phone = 415 935-4228
address = 3 Silver Ct.
city = Walnut Creek
state = CA
zip = 94595
contract = 1
Slot 13 Offset 0x4e6
--------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A0184E6: 00180030 20353136 2d363939 35373238 0...615 996-8275
1A0184F6: 3834494d 01353031 00000009 00330005 MI48105.......3.
1A018506: 0044003f 005e0055 2d323137 312d3534 ?.D.U.^.712-45-1
1A018516: 64373638 43206c65 69747361 496f6c6c 867del CastilloI
1A018526: 73656e6e 36383232 61724320 6c50206d nnes2286 Cram Pl
1A018536: 3823202e 6e6e4136 62724120 726f . #86Ann Arbor
au_id = 712-45-1867
au_lname = del Castillo
au_fname = Innes
phone = 615 996-8275
address = 2286 Cram Pl. #86
city = Ann Arbor
state = MI
zip = 48105
contract = 1
Slot 14 Offset 0x544
--------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A018544: 00180030 20393132 2d373435 32383939 0...219 547-9982
1A018554: 36344e49 01333034 00000009 00330005 IN46403.......3.
1A018564: 0041003b 0052004e 2d323237 352d3135 ;.A.N.R.722-51-5
1A018574: 44343534 61724665 4d65636e 65686369 454DeFranceMiche
1A018584: 4220336c 69646c61 5020676e 61472e6c l3 Balding Pl.Ga
1A018594: 7972 ry
au_id = 722-51-5454
au_lname = DeFrance
au_fname = Michel
phone = 219 547-9982
address = 3 Balding Pl.
city = Gary
state = IN
zip = 46403
contract = 1
Slot 15 Offset 0x596
--------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A018596: 00180030 20353134 2d333438 31393932 0...415 843-2991
1A0185A6: 34394143 00393036 00000009 00330005 CA94609.......3.
1A0185B6: 003f003b 00580051 2d343237 392d3830 ;.?.Q.X.724-08-9
1A0185C6: 53313339 6e697274 44726567 356b7269 931StringerDirk5
1A0185D6: 20303234 656c6554 70617267 76412068 420 Telegraph Av
1A0185E6: 6b614f2e 646e616c .Oakland
au_id = 724-08-9931
au_lname = Stringer
au_fname = Dirk
phone = 415 843-2991
address = 5420 Telegraph Av.
city = Oakland
state = CA
zip = 94609
contract = 0
Slot 16 Offset 0x5ee
--------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A0185EE: 00180030 20353134 2d343533 38323137 0...415 354-7128
1A0185FE: 34394143 01323136 00000009 00330005 CA94612.......3.
1A01860E: 0044003d 00590052 2d343237 392d3038 =.D.R.Y.724-80-9
1A01861E: 4d313933 65466361 65687461 65745372 391MacFeatherSte
1A01862E: 736e7261 55203434 6e616c70 74482064 arns44 Upland Ht
1A01863E: 614f2e73 6e616c6b 64 s.Oakland
au_id = 724-80-9391
au_lname = MacFeather
au_fname = Stearns
phone = 415 354-7128
address = 44 Upland Hts.
city = Oakland
state = CA
zip = 94612
contract = 1
Slot 17 Offset 0x647
--------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A018647: 00180030 20353134 2d343335 39313239 0...415 534-9219
1A018657: 34394143 01393036 00000009 00330005 CA94609.......3.
1A018667: 003e0039 0055004e 2d363537 372d3033 9.>.N.U.756-30-7
1A018677: 4b313933 65737261 76694c6e 37356169 391KarsenLivia57
1A018687: 4d203032 6c754163 53207965 614f2e74 20 McAuley St.Oa
1A018697: 6e616c6b 64 kland
au_id = 756-30-7391
au_lname = Karsen
au_fname = Livia
phone = 415 534-9219
address = 5720 McAuley St.
city = Oakland
state = CA
zip = 94609
contract = 1
Slot 18 Offset 0x69c
--------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A01869C: 00180030 20313033 2d363439 33353838 0...301 946-8853
1A0186AC: 3032444d 01333538 00000009 00330005 MD20853.......3.
1A0186BC: 0041003b 005c0053 2d373038 362d3139 ;.A.S.\.807-91-6
1A0186CC: 50343536 65746e61 5379656c 69766c79 654PanteleySylvi
1A0186DC: 35393161 72412036 676e696c 206e6f74 a1956 Arlington
1A0186EC: 522e6c50 766b636f 656c6c69 Pl.Rockville
au_id = 807-91-6654
au_lname = Panteley
au_fname = Sylvia
phone = 301 946-8853
address = 1956 Arlington Pl.
city = Rockville
state = MD
zip = 20853
contract = 1
Slot 19 Offset 0x6f8
--------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A0186F8: 00180030 20353134 2d363338 38323137 0...415 836-7128
1A018708: 34394143 01313033 00000009 00330005 CA94301.......3.
1A018718: 003f0039 0057004e 2d363438 372d3239 9.?.N.W.846-92-7
1A018728: 48363831 65746e75 65685372 336c7972 186HunterSheryl3
1A018738: 20303134 6e6f6c42 53206564 61502e74 410 Blonde St.Pa
1A018748: 41206f6c 6f746c lo Alto
au_id = 846-92-7186
au_lname = Hunter
au_fname = Sheryl
phone = 415 836-7128
address = 3410 Blonde St.
city = Palo Alto
state = CA
zip = 94301
contract = 1
Slot 20 Offset 0x74f
--------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A01874F: 00180030 20373037 2d383434 32383934 0...707 448-4982
1A01875F: 35394143 00383836 00000009 00330005 CA95688.......3.
1A01876F: 0042003b 0055004c 2d333938 312d3237 ;.B.L.U.893-72-1
1A01877F: 4d383531 64614263 486e6564 68746165 158McBaddenHeath
1A01878F: 30337265 75502031 6d616e74 61636156 er301 PutnamVaca
1A01879F: 6c6c6976 65 ville
au_id = 893-72-1158
au_lname = McBadden
au_fname = Heather
phone = 707 448-4982
address = 301 Putnam
city = Vacaville
state = CA
zip = 95688
contract = 0
Slot 21 Offset 0x7a4
--------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A0187A4: 00180030 20313038 2d363238 32353730 0...801 826-0752
1A0187B4: 34385455 01323531 00000009 00330005 UT84152.......3.
1A0187C4: 003d0039 0059004b 2d393938 322d3634 9.=.K.Y.899-46-2
1A0187D4: 52353330 65676e69 6e6e4172 20373665 035RingerAnne67
1A0187E4: 65766553 2068746e 532e7641 20746c61 Seventh Av.Salt
1A0187F4: 656b614c 74694320 79 Lake City
au_id = 899-46-2035
au_lname = Ringer
au_fname = Anne
phone = 801 826-0752
address = 67 Seventh Av.
city = Salt Lake City
state = UT
zip = 84152
contract = 1
Slot 22 Offset 0x7fd
--------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1A0187FD: 00180030 20313038 2d363238 32353730 0...801 826-0752
1A01880D: 34385455 01323531 00000009 00330005 UT84152.......3.
1A01881D: 003f0039 005b004d 2d383939 332d3237 9.?.M.[.998-72-3
1A01882D: 52373635 65676e69 626c4172 36747265 567RingerAlbert6
1A01883D: 65532037 746e6576 76412068 6c61532e 7 Seventh Av.Sal
1A01884D: 614c2074 4320656b 797469 t Lake City
au_id = 998-72-3567
au_lname = Ringer
au_fname = Albert
phone = 801 826-0752
address = 67 Seventh Av.
city = Salt Lake City
state = UT
zip = 84152
contract = 1
DBCC-Ausführung abgeschlossen. Falls DBCC Fehlermeldungen ...
DBCC-Ausführung abgeschlossen. Falls DBCC Fehlermeldungen ...
DBCC PAGE bietet als vierten Parameter an, wie das Layout der zurückgegebenen Informationen sein sollen. Option 3 ist da mein Favorit.
Tags: Tags: internalsstorage engine
Dieser Eintrag wurde eingetragen von admin und ist abgelegt unter SQL Server. Tags: internals, storage engine Tags: internals, storage engine