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
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.