Sybase SQL Server log issues are pervasive

Every database in Sybase SQL Server, whether it is a system database (Master,model, Sybsystemprocs, tempdb), or a user database, has its own transaction log, each of which has a syslogs table. Log records the user's modifications to the database, so if the command is not cleared, the log will grow up to fill up the space. Clear Log Available DUMP TRANSACTION command, or Open database option trunc Log on chkpt, the database automatically clears log at intervals. Managing database log is one aspect that users must consider when manipulating a database.

Here are a few ways to talk about log and its management:

How SQL Server logs and reads log information

We know that SQL Server is the first log mechanism. Log pages in the Server Cache memory are always written first on the data page:

Log pages are written to the hard drive when a commit, checkpoint,space needed.

Data pages are written to the hard drive when Checkpoint,space needed.

The system reads information from each database's syslogs table at recovery, returns the unfinished Transaction (transaction) (data changes to the transaction state), completes the Committed transaction (transaction) (the data changes to the state after the transaction is committed). Write down the checkpoint point in the log. This ensures the consistency and integrity of the entire database system.

Ii. Transaction logs and checkpoint process

The function of the checkpoint command is to force all dirty pages (pages that have been updated since the last time they were written to the database device) to write to the database device. The automatic checkpoint interval is computed by SQL Server based on the system activity and the recovery interval (recovery interval) value in the system table sysconfigures. By specifying the total amount of time required for system recovery, the recovery interval determines the frequency of checkpoint.

If the database opens the Trunc log on chkpt option, SQL Server automatically clears the log when the database system executes checkpoint. However, the user writes the executed checkpoint command without clearing the log, even if the trunc log on chkpt option is open. SQL Server automatically clears the log only if the trunc log on chkpt option is open, and the checkpoint action is performed by the same time. The process of this automatic checkpoint action in SQL Server is called the checkpoint process. When the trunc log on chkpt option is open, the checkpoint process clears the log every 0 seconds, regardless of the recovery interval set time interval.

Three, Transaction log size

There is no very strict and precise way to determine how much space a database log should give. For a newly created database, the log size is about 20% of the size of the entire database. Log is growing very quickly because log records modify the database and if the changes are frequent. So the log space size depends on how the database is used by the user.

For example:

Frequency of Update,insert and deletes

The amount of data modified in each transaction

SQL Server system parameter recovery interval value

Log is stored on media for database recovery

There are other factors that affect the log size, and we should estimate the log size based on the operation, and then back up and clear the log at intervals of one cycle.