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 unfinished transactions (transaction) (the data changes to the status before the transaction), and completes the committed transactions (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 ProcessesThe checkpoint Command forces all "dirty" pages (pages updated after the last write to the database device) to be written to the database device. The automatic checkpoint interval is calculated by ASE 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 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. 3. The size of the transaction log does not have a very strict and definite method to determine the Log Size 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: The data modification volume in each transaction. The ASE system parameter recovery interval value log is stored on the media for database recovery. Other factors 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 sizeIf the log is on your device, DBCC checktable (syslogs) has the following information: for 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% determines 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 much as 16 pages. use the sp_spaceused command in the syslogs table.
V. Log DevicesGenerally, data and log of a database should be stored on different database devices. The advantage of doing so: You can back up the transaction log separately to prevent the database from overflow and view the usage of the log space. [DBCC checktable (syslogs)] mirror log Devices
Vi. Log clearingDatabase 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.
VII. Managing large transactions
Some operations modify data in large batches, and log growth is very fast, such: A large number of data modifications delete all records of a table insert batch data copies based on sub-queries the following describes how to use these transactions to make the log not overflow: a large number of data modifications examples: 1> Update large_tab set col_1 = 0
2> If the table for go is large, the update operation may fill up the log before it is completed, causing the 1105 error (log full) in addition, execute the exclusive table lock generated by this large transaction to prevent other users from modifying this table during update, which may cause a deadlock. To avoid these situations, we can divide this large transaction into several small transactions and execute the dump transaction. The preceding example can be divided into two or more small transactions. For example: 1> Update large_tab set col1 = 0 2> where col2 3> go
1> dump transaction database_name with truncate_only
2> go
1> Update large_tab set col1 = 0
2> where col2> = x
3> go
1> dump transaction database_name with truncate_only
2> go if the transaction needs to be backed up to the media, the with truncate_only option is not required. If dump transaction with truncate_only is executed, run the dump DATABASE Command first. Delete all records of a table: for example, 1> Delete table large_tab
2> Similarly, go deletes the entire table record and records a lot of logs. We can use the truncate table command instead of the preceding statement to complete the same function. 1> truncate table large_tab
2> in this way, the records in the table are deleted. Using the truncate table command, log only records space collection, rather than the operations on each row in the table. Example of data insertion based on subqueries: 1> insert new_tab select col1, col2 from large_tab
2> In The Same go method, for this large transaction, we should handle several small transactions. 1> insert new_tab
2> select col1, col2 from large_tab where col1 <= y
3> go
1> dump transaction database_name with truncate_only
2> go
1> insert new_tab
2> select col1, col2 from large_tab where col1> Y
3> go
1> dump database database_name with truncate_only
2> go. If you want to save the log to the media, do not add the with truncate_only option after dump transaction. If you execute dump transaction with truncate_only, do the dump database action first. When using bcp to merge data into the database for batch data copying, we can convert this large transaction into several small transactions for processing to avoid log surge. Open trunc log on chkpt Option 1> use master
2> go
1> sp_dboption database_name, trunc, true
2> go
1> Use database_name
2> go
1> checkpoint
2> go BCP...-B 100 (on UNIX)
BCP.../batch_size = 100 (on VMS) disable the trunc log on chkpt option and dump the database. In this example, a batch executes the 100-row copy operation. You can also divide the BCP input file into two or more separate files, and perform dump transaction after each file is executed to avoid full logs. If BCP uses the quick method (no index, no triggers), this operation does not record logs. In other words, logs only record space allocation. In this case, dump the database first (used to restore the database ). If the log is too small, you can set the trunc log on chkpt option to clear the log after each checkpoint.
VIII. Threshold and transaction log management
ASE provides the threshold management function, which helps you automatically monitor free space of the Database Log Device segment. For a detailed discussion in this regard, see No. 5 Technical Support magazine. Log Management is flexible and complex. We should explore the experience in practice and perform different operations for different databases.
2. How to truncate the transaction logs of the database?After the transaction log fills up the log space in the database, you may not be able to use the transaction log dumping method to back up and clear the original log, because the log dumping action also needs to record the log. At this time, you can first use the dump transaction database_name with truncate_only command. This command only truncates/clears transaction logs and does not generate actual backups. If not, run the dump transaction database_name with no_log command. This command only clears the existing transaction logs, does not generate the actual backup file, and does not keep logs. If the command does not work, use the alter database command to allocate additional space for the database logs and then run dump transaction.
3
. Risk of using dump transaction with no_logIn the dump transaction with no_log entry in the command reference manual, there is a warning message that you should use this command only when there is no other method. But what does "last trick" mean? What will happen when you use this command? Which command should you use to replace it? Finally, if this command is so problematic, why does Sybase provide it? Sybase Technical Support recommends that you regularly dump your transaction log. You must determine the dump mode based on the activity volume of logs recorded in your database and the size of your database. Dump transaction on a monthly basis in some places; dump transaction on a daily basis in some places. If you have never done dump transaction, the transaction log will eventually be full. SQL Server uses logs for recovery purposes. When the log is full, the server will stop the transaction, because the server will not be able to write these transactions into the log, and the server cannot run most of the dump Tran commands, because ASE also needs to record these commands in logs. This is why no_log is executable when other dump Tran commands cannot be executed. However, if you think about the environment where dump transaction with no_log is designed and executed, no concurrency check will be performed. If you use dump transaction with no_log when modifying the database, you may be at risk of database crash. In most cases, they are reflected as 813 or 605 errors. Dump transaction with truncate_only can be used to delete the inactive part of the transaction log when the database is modified. This command is written into the transaction log, and it also performs necessary concurrency checks. Both commands have related warnings, which are displayed in the command reference manual. Make sure that you understand the warnings and instructions before using any of the commands. Sybase provides dump transaction with no_log to handle some very pressing situations. To ensure Database Consistency as much as possible, you should take it as the "last move ". 4. Why does dump Tran with no_log still fail to be truncated when the database transaction log is full? This problem may occur in two cases. First, the application system sends a user-defined transaction to ase, which has never been committed. This earliest active transaction hinders the system from intercepting logs. Second, the client sends a transaction with a large number of modifications to ase. When logs are cleared, the transaction is still being executed. Logs involved in the transaction can only be intercepted after the transaction is completed. In the first case, the system administrator can clear the logs as long as the user is urged to exit the application or commit the transaction. Because dump transaction with no-log or with truncate-only is sent to ase, it truncates the inactive part of the transaction log. The so-called inactive part refers to all committed or rolled back transactions between server checkpoints. Transaction logging between the earliest uncommitted transactions and the most recent log records is called active. From then on, we can see that the opened transaction can cause the log to rise, because the logs after the earliest active transactions cannot be intercepted. The same applies to the second case. You must exercise caution when dealing with it. If this large transaction has been running for a long time, try to expand the database log space to ensure that the transaction ends normally. If the transaction is forcibly rolled back, the ASE requires a lot of processing work, which is often several times the positive execution time. The system recovery time is long and may affect the normal use time.
