SYBASE Database Log details

We know that sybase SQL Server uses Transaction to track changes in all databases. A transaction is the unit of work of SQL Server. One transaction contains one or more T-SQL statements that are executed as a whole. Each database has its own Transaction Log (Transaction Log), that is, the system table Syslogs ). The transaction log automatically records each transaction sent by each user. Logs are vital to the security and integrity of database data. We must be familiar with log-related knowledge for database development and maintenance.

1. How does sybase SQL Server record and read log information?

Sybase SQL Server records logs first. Every time a user executes a statement that modifies a database, SQL Server automatically writes changes to the log. After all the changes produced by a statement are recorded in the log, they are written to the data page and copied in the buffer zone. This data page is stored in the buffer, and is not written to the disk until other data pages require this memory. If a statement in the transaction fails to be completed, SQL Server rolls back all changes in the transaction. This ensures the consistency and integrity of the entire database system.

Ii. Log Devices
Log is the same as the Data in the database. You can store Log and Data on the same device or separately. Generally, Data and Log of a database should be stored on different database devices. This method has the following advantages: one is to back up the transaction Log separately; the other is to prevent the database overflow; the third is to see the space usage of the Log.
There is no exact method to determine the size of the created Log Device. Generally, for the newly created database, the Log size should be about 30% of the database size. The Log size also depends on the frequent modification of the database. If the database is frequently modified, the Log increases rapidly. Therefore, the size of Log space depends on how the user uses the database. In addition, there are other factors that affect the Log size. We should estimate the Log size based on actual operations, and back up and clear the Log at intervals.
3. Log clearing
As the database is used, the Log of the database continues to grow and must be cleared before it occupies full space. There are two methods to clear logs:
1. Automatic cleanup

The database option Trunc Log on Chkpt is enabled to enable the database system to automatically clear logs at intervals. The advantage of this method is that it is automatically executed by SQL Server without manual intervention, and generally logs are not full. The disadvantage is that only logs are cleared without backup.
2. Manual cleanup
Run "dump transaction" to clear logs. The following two commands can clear logs:

dump transaction with truncate_only dump transaction with no_log

You can use the "dump transaction with trancate_only" command to delete the inactive parts of the transaction log. When writing this command into the transaction log, you must perform necessary concurrency checks. SYBASE provides "dump transaction with no_log" to deal with some very pressing situations. Using this command is highly risky. the SQL Server displays a warning message. To ensure Database Consistency as much as possible, you should take it as the "last move ".
The preceding two methods only clear logs without backing up logs. to back up logs, run the "dump transaction database_name to dumpdevice" command.
4. Manage huge transactions
Some operations will modify data in large batches, such as modifying and updating a large amount of data), deleting all the data in a table Delete), and inserting a large amount of data into the Insert), which will speed up Log growth, there is a risk of overflow. The following describes how to split large transactions to avoid log overflow.
For example, when the "update tab_a set col_a = 0" command is executed, if the table tab_a is very large, the Update operation may overflow the Log before it is completed, causing the 1105 Error Log Full ), the Exclusive Table Lock generated by executing such a large transaction will prevent other users from modifying the Table during the Update operation, which may cause a deadlock. To avoid this, we can divide this large transaction into several small transactions and execute the "dump transaction" action.
In the preceding example, the transaction can be divided into two or more small transactions:

update tab_a set col_a=0 where col_b>x go dump transaction database_name with truncate_only go update tab_a set col_a=0 where col_b <=x go dump transaction database_name with truncate_only go

In this way, a large transaction is divided into two smaller transactions.
Based on the above method, you can split large transactions as needed. If this transaction needs to be backed up to the media, the "with truncate_only" option is not required. If you run the "dump transaction with truncate_only" command, you should first run "dump database ". Similarly, we can split large transactions such as table deletion and table insertion.

(