tackle high syslog issue in sybase

Question

What I understand from the syslog is that- necessary info is logged into a file so that rollback can be performed based on the info stored into syslog file. From last few months I am facing high syslog usage in my sybase database. Checking on the database activity at the time of high syslog usage, not able to find any query that can cause this. I have "trunc log on chkpt" dboption also enabled.

sp_helpthreshold

 segment name     free pages     last chance?     threshold procedure 
---------------  -------------  ---------------  ----------------------       
 logsegment            109296                1   sp_thresholdaction

Could anybody please point out if any other setting is required to keep syslog usage under control?

result of sp_helpdb DB01

name     db_size        owner     dbid     created       durability     status                                                                   
 -------  -------------  --------  -------  ------------  -------------  ------------------------------------------------------------------------ 
 tlew04      33000.0 MB  sa          13     Apr 17, 2013  full           select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log full 

 device_fragments     size           usage                 created              free kbytes      
 -------------------  -------------  --------------------  -------------------  ---------------- 
 dev29                    1000.0 MB  data only             May 26 2013  5:17AM               928 
 dev32                     250.0 MB  log only              May 26 2013  5:17AM  not applicable   
 dev29                     250.0 MB  data only             May 26 2013  5:17AM                 0 
 dev32                       7.0 MB  log only              May 26 2013  5:17AM  not applicable   
 dev38                      13.0 MB  log only              May 26 2013  5:17AM  not applicable   
 dev29                    1450.0 MB  data only             May 26 2013  5:17AM               240 
 dev38                     230.0 MB  log only              May 26 2013  5:17AM  not applicable   
 dev29                     300.0 MB  data only             May 26 2013  5:17AM               416 
 dev38                     200.0 MB  log only              May 26 2013  5:17AM  not applicable   
 dev29                     500.0 MB  data only             May 26 2013  5:17AM              1230 
 dev38                     300.0 MB  log only              May 26 2013  5:17AM  not applicable   
 dev29                     500.0 MB  data only             May 26 2013  5:17AM               876 
 dev38                     100.0 MB  log only              May 26 2013  5:17AM  not applicable   
 dev29                     200.0 MB  data only             May 26 2013  5:17AM                 0 
 dev38                     200.0 MB  log only              May 26 2013  5:17AM  not applicable   
 dev29                    3200.0 MB  data only             May 26 2013  5:17AM              2316 
 dev38                     400.0 MB  log only              May 26 2013  5:17AM  not applicable   
 dev29                     200.0 MB  data only             May 26 2013  5:17AM                 0 
 dev38                     200.0 MB  log only              May 26 2013  5:17AM  not applicable   
 dev29                   18555.0 MB  data only             May 26 2013  5:17AM           9156764 
 dev35                    3845.0 MB  data only             May 26 2013  5:17AM           3921884 
 dev1                     1100.0 MB  log only              Jun  8 2014  9:06AM  not applicable   


 column1                                               
 ----------------------------------------------------- 
 log only free kbytes = 3059998                        

Answer 1

As seen in your sp_helpdb output, you have 3000Mb of log space, and currently have 2988Mb free, so the problem is not occurring at this time.

Assuming you are not using Sybase Replication Server, what is likely happening is that you have a long running transaction that is keeping the truncation point in the log from moving. This in turn causes the log to fill up before the transaction can commit, and the checkpoint and truncation can occur.

In other words say you have 10 transactions, 1 through 10, executed in order. If 2 through 10 finish, but 1 is still open, the transaction log will not get truncated until 1 finishes.

To see if you have a long running transaction, you will need to check master..syslogshold which shows the oldest running transaction in each database.

There are a couple of things you can try to resolve this issue.

Increase the transaction log size. Currently it appears that you have set the transaction log to be approximately 10% of the size of your data. You could try increasing that to 15-20%, and see if the extra space gives the long running transaction enough time to complete.

The other thing to do is try to figure out what transaction/s are running long, and see if those queries can be optimized to reduce the run time.