Transaction Log in Sybase ASE

Transaction Log in ASE

Database in Sybase ASE has 3 segments by default - Default (for System Objects), Data (To store actual data pages of Tables) and Log Segment (to store log pages). In ASE, we have a write ahead log i.e. before any transaction is applied to database, it is written on to a table called syslogs in log segment to ensure recovery in even of failure.

On a high level, transaction log has two portions - the active portion and inactive portion. Inactive portion of transaction log has committed and rolled back transactions and this portion can only be truncated.

Active portion of transaction log has open transactions also in addition to committed and rolled back transactions.

There is also a marker called Truncation Point in transaction log which marks the end of inactive portion and beginning of active portion. (This marker is just 1-2 pages behind the end of inactive portion). Refer to figure below for further clarification -

To understand logging in Transaction log further, Let's assume that following command is issued against  an ASE database -

begin tran

INSERT INTO TABS ('1','ARDENTDBA','INDIA')

commit

Following logical high level process will happen in background -

•     Begin tran would be logged in syslogs table of database
•     Identified extent of table TABS would be bought into cache by ASE
•     Insert statement would be logged in syslogs table
•     Identified page in cache of TABS table will be written  with this new row's  data
•     Commit statement would be logged in syslogs table
•     Page of TABS table which is written with new row will be marked as dirty page
•     This dirty page would be written to disk during checkpoint.

So as Transaction log stores all the transactions running against a database, transactions get accumulated here with time, which in turn starts filling log segment of the database. As a DBA, Its your responsibility to manage this table so that the log segment doesn't fills up and bring your database to stand still. You can choose either to archive-no truncate, archive-truncate or truncate the inactive portion of Transaction log depending on the criticality of the database.  For mission critical databases, were we need to ensure full recovery of data, we archive-truncate the transaction log. For dev/test environments where data is not very critical, we truncate transaction log. Archive-no truncate is used during special case of recovery from Database failure.

Dump Transaction command in Sybase ASE is used to manage transaction log of database. For example,

• Backup Transaction Log to File issue following command
• dump transaction <databasename> to <filename>
• To truncate transaction log, issue following command
• dump transaction <databasename>  with truncate_only
• To archive transaction log without truncating the log, use below command
• dump transaction <databasename>  with no_truncate

Ardent-DBA