Database option 'trunc log on ckpt' is eabled, but still received error 1105 in sybmgmtdb. Why?

[former_member207908]

Dear Experts,

Database name: sybmgmtb (Size: 272 MB)

db option: trunc log on ckpt is turned on

ASE/OS: 15.7 SP122 on AIX 7.1

Environment: OLTP + OLAP

Issue: Error 1105 in sybmgmtdb

Temp fix I've done: increased the space by 100 MB and the issue was cleared. Now looking for a permanent fix

dump tran can only dump the committed transactions, right?

So the issue in this case is LARGE trasactions, which needs to be prevented by declaring and enforcing a standard.

Need your help (clear steps) in establishing a standard for the transactions (ex 500 rows for every transaction commit )

Let me know if you need more information. I want ot fix this permanently

Awaiting for you indepth valuable suggestions

Regards,

Rajesh

[victoria_normand · Accepted solution]

Hi Rajesh,

About the "trunc log on chkpt" database option will truncate the transaction log, that means to remove the committed transactions, when an "automatic checkpoint" occurs, that is, approximately once a minute. What the automatic checkpoint does? It checks each database on the server to see how many records have been added to the transaction log since the last checkpoint. If the server estimates that the time required to recover these transactions is greater than the database’s recovery interval, SAP ASE issues a checkpoint, and therefore the committed transactions are removed.

About your question: "dump tran can only dump the committed transactions, right?" yes, totally right.

About the "Need your help (clear steps) in establishing a standard for the transactions (ex 500 rows for every transaction commit )" withtout the checks done by the automatic checkpoint, this would be difficult to answer for any database, it will depend not only of the number of rows, but also the size, and a transaction may involve more than 1 table. There is also configurations parameters playing here but I do not think it would be relevant for this particular database issue.

Sybmgmtdb database contains all the job, schedule, and scheduled job information and it also maintains the output and results from these executed tasks. So there shouldn't be the concept of large transaction here unless your jobs are generating big outputs results. Normally the logs generated by ATM (the one that generate more outputs) should be truncated, but this was a very old behavior solved with SAP Note 1665820 - SYB: Job scheduler logs for ATM are not truncated
Can you confirm your system is at least on those SP levels: 7.02 SP12, 7.30 SP8 or 7.31 SP5 ?

Best regards,
Victoria.

[former_member3043]

Hello,

sybmgmtdb is used by the JobScheduler, there shouldn't be any long running transactions.

Is the logsegment reported with the 1105 or maybe default or system ?

Maybe you can post the errorlog snapshot of this 1105 error.

With kind regards

Stefan

[former_member207908]

Dear Stefan,

Thank you for your response and please find the details of my system

System Characteristics Database Name: C30 Database Host: egac30db Schema: C30.SAPSR3 Database Release: 15.7.0.122 Database Platform: Sybase ASE Database Operating System: AIX SAP ABAP System: Basis Release: 731

error snapshot:

00:0004:00000:00018:2014/11/23 10:30:22.56 server  Can't allocate space for object 'syslogs' in database 'sybmgmtdb' because 'logsegment' segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE to increase the size of the segment.

Space available in the log segment has fallen critically low in database 'sybmgmtdb'.  All future modifications to this database will be suspended until the log is successfully dumped and space becomes available.

1> sp_helpdb sybmgmtdb
2> go
name           db_size    owner  dbid   created     durability  lobcomplvl  inrowlen               status                                                                                                                                                                                                                      
------------------------------------ ---------------------------------------------------- -------------------- -------------------- -----------------------------------------------------

sybmgmtdb  272.0 MB   sa   31515   Jul 10, 2013  full         0 NULL  select into/bulkcopy/pllsort, trunc log on chkpt, mixed log & data                                                                                                                                         

device_fragments        size          usage              created                         free kbytes
------------------------------------------- -------------------- ------------------------- ------------------------------------
sybmgmtdev            72.0 MB    data and log       Jul 10 2013  6:01AM          42336
sybmgmtdev           100.0 MB   data and log       Nov 17 2013 10:01AM        88864
sybmgmtdev           100.0 MB   data and log       Nov 23 2014 10:31AM        101872

Regards,

Rajesh

[former_member188958]

what does "select * from master..syslogshold" show as being the oldest open transaction for sybmgmtdb?

[former_member207908]

Sorry, I did not take that output during that time.