SAP Knowledge Base Article - Preview

2747756 - sp_spaceused "reserved_pages" value keeps growing - SAP ASE

Symptom

  • sp_spaceused reserved_pages value keeps growing
  • sp_spaceused syslogs
    go
     name            total_pages     free_pages      used_pages      reserved_pages
     --------------- --------------- --------------- --------------- ---------------
     syslogs         16357376        14630165        1652095         75116
  • reserved_pages only decrements if ASE is recycled
  • Problem is linked to utilization of the option "delayed commit"

    • This option "delayed commit" can be set as a database option. sp_helpdb shows that "delayed_commits" is enabled:

sp_helpdb <database>
go

 name           db_size           owner              dbid   created                durability  lobcomplvl  inrowlen   status
-------------   -------------     ----------------- ----    ------------           ----------   ----------    --------     -----------------------------------------------------------------------
<database>   271052.0 MB  <owner>         4        Aug 22, 2018       full            0               NULL        select into/bulkcopy/pllsort, delayed commit, deferred table allocation

    • This option can be set at connection level with the same effect. Following command can be used to verify if option is set at connection level:

dbcc pss
go | grep -E 'pspid=|OPT_DELAYED'

      • When only 1 line is displayed, this means the connection pspid is not using "delayed commits"

pkspid=3604508 pspid=17 pclient_kpid=3604508 parent_spid=17

      • When 2 lines are displayed, this means the process pspid is using "delayed commits"

pkspid=23003222 pspid=25 pclient_kpid=23003222 parent_spid=25
(OPT_DELAYED_COMMIT) 84 (OPT_LITERAL_AUTOPARAM)

    • This option can be set at connection level by the DSI connection from Replication Server with parameter dsi_non_blocking_commit. Verify if it is set by executing in Replication Server the following command:
      • Connect to Replication Server, and execute :

connect to RSSD
go

select optionname, connection_id=
case
when convert(bigint, objid)=0 then convert(char(10),' GLOBAL')
when convert(bigint, objid)!=0 then convert(char(10),str( hextoint( bintostr( substring(objid, 1, 4) ) ), 13, 0))
end,
charvalue=substring(charvalue,1,10)
from rs_config
where optionname = "dsi_non_blocking_commit"
go

      • If all the rows displayed a value of 0 for the column charvalue, the option dsi_non_blocking_commit is not used

optionname                       connection_id    charvalue
------------------------------ -----------------  ----------------------------------------
dsi_non_blocking_commit   GLOBAL            0

      • If the row with connection_id = "GLOBAL" displayed a value of 1 for the column charvalue, the option dsi_non_blocking_commit will be used for all the DSI connection.

optionname                       connection_id    charvalue
------------------------------ -----------------  ----------------------------------------
dsi_non_blocking_commit   GLOBAL            1

      • If a row with connection_id = <connection ID> displayed a value of 1 for the column charvalue, the DSI connection <connection ID> is using option dsi_non_blocking_commit to connect to the destination.

optionname                       connection_id    charvalue
------------------------------ -----------------  ----------------------------------------
dsi_non_blocking_commit   GLOBAL            0
dsi_non_blocking_commit                  114   1

=> In this output, we can see that the option is not set at Replication Server level, but it is set for the DSI connection with ID = 114


Read more...

Environment

SAP Adaptive Server Enterprise (ASE) 16.0

Product

SAP Adaptive Server Enterprise 16.0

Keywords

sybase, ASE, delayed commit, reserved_pages, syslogs, sp_spaceused, CR 819222, #819222, HADR, SRS , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Bug Filed

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.