Popularity of Sybase SQL Server logs (1)

Each database of Sybase SQL Server, 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 SQL Server record and read log information?

We know that SQL Server records logs first. 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 event is submitted ). 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 is used to force all "dirty" pages to be written to the database device. The automatic checkpoint interval is calculated by SQL Server 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, SQL Server automatically clears the log when the database system executes the checkpoint option. 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, SQL Server automatically performs the checkpoint action to automatically clear logs. The process of this automatic checkpoint action in SQL Server is called the checkpoint process. When the trunc log on chkpt option is enabled, the checkpoint process clears the log every about 0 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

◆ SQL Server 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.