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 for Me (Login required).

Search for additional results

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