Explanation of the management skills of Sybase Database ASE transaction logs (1)

Sybase ase transaction log

Each database of sybase ase, whether it is a system database master, model, sybsystemprocs, tempdb) or a user database, has its own transaction log, and each database has a syslogs table. Logs record user operations on the database, so if you do not need to clear the Log, the log will continue to grow until the occupied space. You can run the dump transaction command to clear logs, or use the trunc log on chkpt option to clear logs automatically at intervals. Managing database logs is a must for users to operate databases.

Here we will talk about log and its management in several aspects:

I. How does ASE record and read log information?

We know that ASE is a log-first mechanism. Log pages in Server Cache Memory are always written on the data page first:

◆ Log pages are written to the hard disk at commit, checkpoint, and space needed.

◆ Data pages are written to the hard disk at checkpoint and space needed.

The System reads the syslogs table information of each database during recovery, rolls back the unfinished transaction (transaction) data to the status before the transaction), and completes the committed transaction (transaction) data changes to the status after the transaction is committed ). Record the checkpoint in the Log. This ensures the consistency and integrity of the entire database system.

Ii. Transaction logs and checkpoint Processes 

The checkpoint Command forces all "dirty" pages that have been updated since the last time the database device was written) to the database device. The automatic checkpoint interval is calculated by ASE based on the system activity and the recovery interval in the system table sysconfigures. By specifying the total amount of time required for system recovery, the recovery interval determines the checkpoint frequency.

If the trunc log on chkpt option is enabled for the database, ASE automatically clears the log when the database system executes the checkpoint. However, the checkpoint command written and executed by the user does not clear the log, even if the trunc log on chkpt option is enabled. Only when the trunc log on chkpt option is enabled, ASE automatically performs the checkpoint action to automatically clear logs. The process of this automatic checkpoint action in ASE is called the checkpoint process. When the trunc log on chkpt option is enabled, the checkpoint process clears the log every 60 seconds, regardless of the time interval set by recovery interval.

Iii. Transaction log Size

There is no strict or definite method to determine the log of a database. For a new database, the log size is about 20% of the total database size. Because the log records the changes to the database, if the changes are frequent, the log increases rapidly. Therefore, the size of log space depends on how the user uses the database. For example:

◆ Update, insert, and delete frequency
◆ Data modification volume in each transaction
◆ ASE system parameter recovery interval value
◆ Whether the log is stored on the media for database recovery

There are other factors that affect the log size. We should estimate the log Size Based on the operation, and back up and clear the log at an interval.

Iv. Check log size 

If the log is on your device, dbcc checktable (syslogs) has the following information:

Example: *** NOTICE: space used on the log segment is 12.87 Mbytes, 64.35%
*** NOTICE: space free on the log segment is 7.13 Mbytes, 35.65%

Determine whether to use the dump transaction command to back up and clear logs based on the proportion of the remaining log space.

Use a quick method to determine the extent to which the transaction log is full.

◆1>use database_name ◆2>go ◆1>select data_pgs (8,doampg) ◆2>from sysindexes where id=8 ◆3>go

Note: this query may be off by as provided as 16 pages.
Use the sp_spaceused command in the syslogs table.

V. log Devices 

Generally, data and log of a database should be stored on different database devices. Benefits:

◆ You can back up the transaction log separately.
◆ Prevent database Overflow
◆ You can see the usage of the log space. [Dbcc checktable (syslogs)]
◆ Mirror log Devices

Vi. log clearing 

Database logs are constantly increasing and must be cleared before it occupies full space. As discussed earlier, clearing logs can enable the database option trunc log on chkpt, enable the database system to automatically clear logs at intervals of time, and run the command dump transaction to clear logs. the trunc log on chkpt option is the same as the dump transaction with truncate_only command, but the log is cleared without being retained to the backup device. Therefore, if you only want to clear logs without backing up, you can use the trunc log on chkpt option and dump transaction with truncate_only, dump transaction with no_log command. To back up data, dump transaction database_name to dumpdevice.