Category: ASE: dbcc

keep count

keep count is a marker to determine how many users are using a database. The keep count value can be displayed using the command dbcc dbtable().

Display the keep count value of a database

    -- print dbcc output to standard out
    dbcc traceon(3604)
    go

    -- check keep count
    dbcc dbtable(db_name)
    go

The output looks similar to below:

    DBTABLES:

    DB Table at: 0x10012bfec40

    dbt_dbid=7   dbt_stat=0x100c (0x1000 (DBT_SINGLE), 0x0008 (DBT_AUTOTRUNC), 0x0004 (DBT_SELBULK))
    dbt_stat2=0x 0 (0x0000)
    dbt_stat3=0x   0 (0x00000000)
    dbt_stat4=0x   0 (0x00000000)
    dbt_state=0x2(0x0002 (DBST_ACTIVE))   dbt_keep=0  
    dbt_hdeskeep=0 dbt_next=0x10012bc9800   dbt_systask_keep=0 dbt_detachxact_keep 0
    dbt_lock=0  dbt_dbaid=3
    dbt_verstimestamp= Nov 19 2008  8:04PM   dbt_dbname=db_name
    dbt_logrows=0   dbt_extstat=0x0
    dbt_lastlogbp=0x101338e5d60
    dbt_nextseq=457665760  dbt_oldseq=457665759
    dbt_logvers=6
    dbt_upgdvers=31 ... dbt_upgd_minor=1418
    dbt_dbinfovers=5
    dbt_sarg_vers=2  dbt_threshstat=0x0
    dbt_thresholds=0x10012bff5c8  dbt_thresh_spin=0x100004afc00

    dbt_maxthresh=256
    dbt_nextid=194096701 dbt_nextidstat=0x0
    dbt_dflinfo=0x0  dbt_dflstat=0x0
    dbt_dumpthreadlock=0
    dbt_dbts=0x0000 0x52cc16f7   dbt_xdesqueue   next=0x10012bfecc0 
    prev=0x10012bfecc0
    dbt_xdesqueue_spin=0x100004afa00
    dbt_seqspin=0x100004afac0
    dbt_append_log_reqs=0  dbt_append_log_waits=0
    dbt_commits=0x0000 0x52cc16f7   
    dbt_commitsqueue   next=0x10012bfed80 prev=0x10012bfed80
    dbt_lastckptts=0x0000 0x52cc16f7
    dbt_lwm_ts    =0x0000 0x00000019

    dbt_desqueue 0x10012bff0d8 next=0x10010a34370 prev=0x10012a25328
    dbt_udes=0x10012bff1d8 dbt_mastxlate=0x1000001f978 dbt_xlate=0x10012bff0f0
    dbt_dbaudflags=0x0 dbt_deftabaud=0x0 dbt_defviewawd=0x0 dbt_defpraud=0x0
    dbt_rep_stat=0x0, dbt_rep_work=0x0, dbt_rep_level=0

    dbt_xstat=0  dbt_slotid=15  dbt_ddlcount=0
    dbt_logxlate=0x0 dbt_logxlate_maxoff=0
    dbt_protstamp=0
    dbt_logflush=457665758
    dbt_backup_start: Nov 21 2008  4:13PM
    dbt_pagelkprom=[lwm=200 hwm=200 pct=100 status=(0x0008 (LKPROM_PAGE), 
    0x0001 (LKPROM_SERVER)) seqno=1]
    dbt_rowlkprom=[lwm=200 hwm=200 pct=100 status=(0x0010 (LKPROM_ROW), 
    0x0001 (LKPROM_SERVER)) seqno=1]

    dbt_singleuser=0x0 dbt_seg=0x10012bff2c8
    dbt_logsuspended=0
    dbt_repl_context=0x10012bffde8  dbt_repl_spin=0x100004afb80
    dbt_secondary_truncpg=00x0
    dbt_rep_gen_id=0
    dbt_dbcache=0  dbt_sysindcache=-1
    dbt_csysindcache=-1 ha_suspect_info=0x0
    dbt_supergam array
           72            0            0            0            0
            0            0            0            0            0
            0            0            0            0            0
            0            0            0            0            0
            0            0            0            0            0
            0            0            0            0            0
            0            0            0            0            0
            0            0            0            0            0
            0            0            0            0            0
            0            0            0            0            0
            0            0            0            0            0
            0            0            0            0            0
            0            0            0            0            0
            0
    dbt_logsize=2550000,  dbt_plcspace=0 dbt_dbinstcount 0
    dbt_logallocs: high=0, low=106053545
    dbt_logallocs_at_recalc: high=-1, low=-1
    dbt_logdeallocs_at_recalc: high=-1, low=-1
    dbt_logfreespace_from_recalc=0

    Async Log service disabled 

    Database Disk Map:

    DMAPFM (0x10026de0000) contains 1 frame; UNRSVDFM (0x10012bfef88) contains 1 frame:
    lstart is logical page #; vstart is virtual (2K) page #; size is # of logical pages.

    dm_segmap: 0x00000003 (0x00000002 (SEG_DEFAULT), 0x00000001 (SYS_SEGMENT))
    dm_lstart=0 (dm_hi_lpage=2047999) dm_vstart=1090519040 (dm_hi_vpage=1094615039)
    dm_lsize=2048000   *dbt_unrsvd=91134

    dm_segmap: 0x00000004 (0x00000004 (LOG_SEGMENT))
    dm_lstart=10240000 (dm_hi_lpage=11263999) dm_vstart=503316480 (dm_hi_vpage=505364479)
    dm_lsize=1024000   *dbt_unrsvd=1020000

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

Problems with keep count

Problems can arise during an attempt to load a database with a dump or to set it in "single user mode". Due to a bug in older versions of ASE the value of keep count can sometimes be larger than 1 although the database is no longer used by anyone. As a consequence, the database can neither be loaded nor set into "single user mode". In these cases, the database must be removed from cache. In ASE version 12.5.3, this can be achieved using the command dbcc dbcacheremove().

    dbcc dbcacheremove(db_name)
    go
    Attempt to uncache the database 'db_name' with dbid 4.
    The descriptors keep count is 1 and the system tasks keep count is 0.
    The descriptors hot count is 0 and the detached keep count is 0.
    The per process keep count for spid 41 is 1.
    The database is set to current by spid 41.
    The database 'db_name' could not be uncached.
    
    DBCC execution completed. If DBCC printed error messages, contact a user with 
    System Administrator (SA) role.

In the above example, the database could not be removed from cache. If the described problems still persist, the only workaround remaining is to reboot the dataserver. When using ASE versions 12.5.4 ESD#1 or v15.0.2 or higher, another workaround could be using dbcc dbreboot. If the database could be removed successfully from cache, keep count will be 0 and loading the database or setting it to "single user mode" will succeed.