how/when does syslogs get cleaned? who's filling syslogs?

[Former Member]

--- SYBASE VERSION ---

Adaptive Server Enterprise/15.7.0/EBF 22639 SMP SP52 /P/Sun_svr4/OS 5.10/ase157sp5x/3293/64-bit/FBO/Wed May 21 02:49:14 2014

--- END ---

Hi community,

I'd like to know how and when does the syslogs get 'flushed' one a transaction is commited and the data are written data data physical disk?

I'd like to also know if there's way to know how long this flush lasts?

Why?

Let's imagine I have an update on a huge table taking 45 minutes then it's followed by other DML statement. This batch of operations generates a LOG SUSPEND therefore we need to increase the log.

If I execute individually those statement, the transaction logs is OK

Therefore my assumption is the time the syslogs is not flushed fast enough and other statement fills it.

Therefore I'd like to understand its flushing mechanism and if there are ways to improve it.

Moreover, is there a way to know the transactions filling it? I'd just be interested by transaction ID and the size used.

Thanks

Simon

[former_member188958 · Accepted solution]

Hi Simon,

There is a possible terminology problem with your question.   "flushing" usually refers to writing a modified (dirtied) page from cache to disk.   ASE uses what is called a "write ahead log", meaning that the transaction log records are always written out before the affected data pages are written out.  When a transaction commits, all the log records for it are flushed to disk before the "commit" command returns success to the client.  The data pages in cache are not flushed at this time (they are flushed periodically by checkpoints issued by the checkpoint process, the user, dump commands, and regular shutdown).  data pages are also flushed as they enter the "wash region" of the cache.

It sounds more like you are asking about truncation (deallocation) of the inactive part of the log.

There are several scenarios:

dump tran with truncate_only simply deallocates the inactive part of the log and is usually quite quick.

dump tran to a device copies the entire log to the external device (disk or tape drive) and then deallocates the inactive part of the log.   dump tran may be blocked by a concurrent dump database.

There are also "relaxed durability" databases that may not write the log records at all.

The time taken depends on whether the allocation pages for the inactive part of the log are in cache (if not they have to be read in from disk), and the speed of your archive devices.

The best predictor will be to time how long it takes on your own system for logs in various states of fullness and interpolate.

As to the question of "what is filling the log", please see my SCN writeup ?

-bret

[Former Member · Accepted solution]

Hi Bret,

Indeed you're right. By flushing I meant truncate the transaction logs.

Then while reading your message, I just remembered my database has the option 'trunc log on chkpt'. Therefore the checkpoint will be in charge of such tasks.

Now, is there a way to speed-up the transaction logs truncation --knowing that I only have one or two databases on my dataserver-- to prevent any TRANSACTION LOG FULL because it gets filled faster than truncated.

Again my suspected test case is one big transaction filling the transaction log and it doen't get truncated fast enough while smaller transactions filled it.

I'm not sure adding checkpoint processes will help. Maybe I should run my test again with several sp_sysmon running to see how long the checkpoint process lasts.

Thanks,

Simon