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

• 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

SAP Adaptive Server Enterprise (ASE) 16.0

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