0

There is a famous undocumented DBCC PAGE command in SQL Server (as well as in SAP ASE) which if successful sends output to errorlog.

Noticed that it has 4 main sections: Buffer, Page Header, Data, and Offset Table.

The question is we notice dbid in the Page Header section. Similarly, there is a bdbid in the buffer section.

Can we unquestionably say that both these ids will be the same? If anyone has experience around these.

For reference: https://techcommunity.microsoft.com/t5/sql-server/how-to-use-dbcc-page/ba-p/383094 https://www.itprotoday.com/sql-server/using-dbcc-page

1

I don't think its very much documented on Microsoft Site and other places. Possibly someone from Microsoft could shed some light on this, doing some checking on Paul Randal's site and other related information, most likely it is database id. You can read more details on this command at this page by him.

There are few more details at this site as below:

The BUFFER: section indicates the buffer page number (bpage) which represents exactly where in the buffer pool the page exists. Also included are:

  • bhash – The hash of the page
  • bpageno – the file and page number
  • bdbid – the database ID
  • references – the number of processes that have referenced the buffer page.
  • bcputicks – the amount of time latches have waited to access the page
  • bsampleCount – the number of times SQL Server has sampled the latch wait time for this page
  • bUse1 – the duration in seconds since the bcputicks and bsampleCount counters have been reset. This is perhaps a reasonable proxy for the amount of time the page has been in the buffer pool.
  • bstat, bstat2 – the status of the buffer page.
  • blog – the transaction log record where the page was last modified
  • bnext – the address of the next page in the buffer pool for this object
  • bDirtyContext – I’m guessing, but this looks like a pointer to the memory structure used by SQL Server’s storage engine to keep track of dirty pages.

As far Sybase ASE is concerned, I checked myself on multiple databases by running this command and could figure out that bdbid is dbid itself. Even if you pass a page number which is not present, it would still give you the same bdbid as that of dbid.

Below is the output from Sybase ASE when page is not present:

------------------------ Execute ------------------------

Page not found in Cache: default data cache.
Page read from disk.

BUFFER:
Buffer header for buffer 0xc00000039cd98000 (Mass head)
    page=0xc00000039cd96000 bdnew=0x0000000000000000 bdold=0x0000000000000000 bhash=0x0000000000000000 baccess_count=0
    bmass_next=0x0000000000000000 bmass_prev=0x0000000000000000 bdbid=6 bmass_encr=0x0000000000000000
    bvirtpg=[ 0xc00000039cd980d8 vpgdevno=0 vpvpn=45540 vdisk=0xc00000010939f230 ]
[Logical device name = xxxx]
    bmass_head=0xc00000039cd98000 bmass_tail=0xc00000039cd98000
    bcache_desc=0xc00000039cd9f178 (cid=0 cache name='default data cache')
    bpool_desc=0xc00000039cda4b30 bdbtable=0x0000000000000000
    Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0
    bmass_size=8192 (8K pool) bunref_cnt=0
    bdestroy_spid=0 bdestroy_curcmd=0 (GENERIC_TOKEN)
    bmass_stat=0x800(0x0000000000000800 (MASS_NOTHASHED))
    blfb_status=0x0000000000000000(0x00000000)
    bbuf_stat=0x0 (0x00000000)
     bcas_wait_cum:0 bcache_spin:0 bversion:0 bhot_keep:0    Buffer blpageno=120 bpg_size=8k Mass blpageno=120 Mass blocallockkeep=0    bxls_pin=0x0000000000000000 bxls_next=0x0000000000000000 bspid=0
    bxls_flushseq=0 bxls_pinseq=0 bcurrxdes=0x0000000000000000 bssdbuf_ptr=0x0000000000000000     LFB delta=0x0000000000000000

Latch and the wait queue:
Latch (address: 0xc00000039cd98040)







Latch wait queue:

PAGE HEADER:
Page header for page 0xc00000039cd96000
pageno=120 nextpg=120 prevpg=120 ptnid=28 syspartitions timestamp=0000 00000227
oampgcount=1 attrcount=10 indid=4: totalentries_lo=1 entrycount=1
page status bits (pstat): 0x800c (0x8000 (PG_OAMPG), 0x0008 (PG_OAMATTRIB), 0x0004 (PG_OAMSORT))

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
------------------------- Done --------------------------

Below is scenario when page is present in the database:

------------------------ Execute ------------------------

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

BUFFER:
Buffer header for buffer 0xc00000039cd98000
    page=0xc00000039cd96000 bdnew=0xc000000c677bf780 bdold=0xc000000c677bf780 bhash=0xc000000c8c1ff8c0 baccess_count=57
    bmass_next=0xc000000c73fcbbc0 bmass_prev=0xc000000c8c1ff8c0 bdbid=4 bmass_encr=0x0000000000000000
    bvirtpg=[ 0xc00000039cd980d8 vpgdevno=4 vpvpn=484 vdisk=0xc0000001093a01b0 ]
[Logical device name = xxxx]
    bmass_head=0xc000000c677bf780 bmass_tail=0xc000000c677bf780
    bcache_desc=0xc00000039cd9f178 (cid=0 cache name='default data cache')
    bpool_desc=0xc00000039cda4b30 bdbtable=0xc0000001692ffa80
    Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0
    bmass_size=8192 (8K pool) bunref_cnt=0
    bdestroy_spid=133 bdestroy_curcmd=260 (ONLINE_DB)
    bmass_stat=0x4001010(0x0000000004000000 (MASS_DONT_DISCARD), 0x0000000000001000 (MASS_HASHED), 0x0000000000000010 (MASS_INWASH))
    blfb_status=0x0000000000000000(0x00000000)
    bbuf_stat=0x1 (0x00000001 (BUF_PG_REF))
     bcas_wait_cum:0 bcache_spin:0 bversion:10566 bhot_keep:0    Buffer blpageno=121 bpg_size=8k Mass blpageno=121 Mass blocallockkeep=0    bxls_pin=0x0000000000000000 bxls_next=0x0000000000000000 bspid=0
    bxls_flushseq=0 bxls_pinseq=0 bcurrxdes=0x0000000000000000 bssdbuf_ptr=0x0000000000000000     LFB delta=0x0000000000000000

Latch and the wait queue:
Latch (address: 0xc00000039cd98040)







Latch wait queue:

PAGE HEADER:
Page header for page 0xc00000039cd96000
pageno=121 nextpg=122 prevpg=0 ptnid=6 syscomments timestamp=0005 54dcda6e
nextrno=47 level=0 indid=0 freeoff=8010 minlen=20
page status bits (pstat): 0x911 (0x0800 (PG_XHEADER), 0x0100 (PG_ADDEND), 0x0010 (PG_RNOFREE), 0x0001 (PG_DATA))
second set of page status bits (dol_stat2): 0x02 (0x02 (PG2_DOL_DATAPG))
dol_pprivstat status bits: 0x01 (0x01 (DOLPG_PRIV_COMMITTED))
pagesize=8192, page version=1, ncfs=38, ndeleted=1, insert free space=124
pagetailts=0xda6e

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
------------------------- Done --------------------------
4
  • Is it a known version specific behaviour that in the second output(when page is present) the page header section doesn't have dbid.? Feb 26 '21 at 8:43
  • I guess so because bdbid is dbid here. Feb 26 '21 at 11:53
  • Can I know your ASE version for the above example where we had page present? Mar 5 '21 at 10:09
  • It is ASE 16. I tested on 15.7 also. Mar 5 '21 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.