cancel
Showing results for 
Search instead for 
Did you mean: 

How to check frequency of the checkpoint to truncate the transaction log

Former Member
0 Kudos

Dear experts,

If i run below:

sp_dboption SID, 'trunc',true

How can I check frequency of the checkpoint to truncate the transaction log?(Every how many minutes/hours?)

Regards,

Fadzly Iqbal

Accepted Solutions (1)

Accepted Solutions (1)

former_member188958
Active Contributor
0 Kudos

The checkpoint process(es) run once a minute and check each database to see if a checkpoint needs to be issued.  If "truncate log" is not set on, this is determined by the sp_configure option "recovery interval in minutes" (which is horribly misnamed).  If "truncate log" is on, then a checkpoint is issued if there have been more then a few (~20) log records since the last checkpoint was issued (the logic being to have enough activity to likely fill a log page so that there would be something to truncate).

"recovery interval in minutes" is badly named because it actually corresponds to a number of log records since the last checkpoint using an old estimate of recovery being able to process so many thousand log records per minute.  With modern hardware, that estimate is now very out of date - and with the possibility of long running transactions means that it really doesn't provide a limit on the amount of time recovery will take anyway.

-bret

Answers (1)

Answers (1)

victoria_normand
Contributor
0 Kudos

Hi Fadzly,

Just in case / for information: for SAP Business Suite on ASE, the "trunc log on chkpt" has to be disabled for your SID at least on production environment (this is a requirement). The transaction log has to be dumped to some device to ensure recoverability of your environment (you can schedule from DBACockpit).


Best regards,
Victoria.