The log of Sybase database management and maintenance experience

Sybase is a world-renowned database manufacturer, and its relational database products Sybase SQL Server has a large number of users in large and medium-sized enterprises in China. The author in the course of many years of use, summed up the Sybase database management and maintenance of some of the experience, now take out to share with you.

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

I. How SYBASE SQL Server logs and reads log information

SYBASE SQL Server is the first log-memory mechanism. Whenever a user executes a statement that will modify the database, SQL Server automatically writes the changes to the log. All the changes generated by a statement are recorded in the log, and they are written to the data page in a copy of the buffer. The data page is saved in a buffer, and the data page is written to disk until the memory is required by another data page. If a statement in a transaction does not complete, SQL Server rolls back all the changes generated by the transaction. This ensures the consistency and integrity of the entire database system.

Second, the log equipment

Log and database data, need to be stored on the database device, log and data can be stored on the same device, can also be stored separately. In general, you should store the data and log of a database on different database devices. This has the following advantages: First, you can back up the backup transaction log separately, the other is to prevent the database overflow, the third is to see log space usage.

There is no very precise way to determine the size of the log device being built. In general, for a new database, the log size should be about 30% of the size of the database. The log size also depends on how frequently the database is modified. If the database is modified frequently, the log grows very quickly. So the log space size depends on how the database is used by the user. In addition, there are other factors affecting the log size, we should be based on the actual operation of the estimated log size, and interval of time to back up and clear the log.

Third, the removal of the log

As the database is used, the log of the database is growing and must be cleared before it fills up space. There are two ways to clear log:

1. Automatic Removal method

Open database option Trunc Log on chkpt, so that the database system automatically clears log every once in a while. The advantage of this approach is that it is performed automatically by SQL Server without human intervention, and that the log overflow is generally not present; The disadvantage is that only log is cleared and no backup is done.

2. Manual removal method

Execute the command "DUMP transaction" to clear the log. The following two commands can clear the log:

Dump TRANSACTION WITH TRUNCATE_ONLY

Dump TRANSACTION WITH NO_LOG

Typically, deleting inactive parts of the transaction log uses the "DUMP TRANSACTION with Trancate_only" command, which, when written into the transaction log, also does the necessary concurrency checks. Sybase provides a "dump transaction with NO_LOG" to handle some of the most pressing situations where it is risky to use this command, and SQL Server pops up a warning message. In order to ensure the consistency of the database as much as possible, you should use it as a "last resort".

Both of these methods only clear the log, not do log backup, if you want to back up the log, you should perform the "DUMP TRANSACTION database_name to Dumpdevice" command.

Iv. managing a huge business

Some operations can modify data in large quantities, such as a large amount of data modification (Update), delete all the data (delete) of a table, insert a large amount of data, which can make log grow fast and full of danger. The following author to introduce you how to split large business, to avoid the overflow of the log.

For example, if the table tab_a is large when the update tab_a set col_a=0 command is executed, the update action may overflow the log before it is completed, causing a 1105 error (Log full). and executing the exclusive lock generated by such a large transaction (Exclusive table lock) prevents other users from modifying the table during the update operation, which can cause deadlocks. To avoid this, we can divide this large transaction into small transactions and execute the "DUMP transaction" action.

The case in the previous example 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.

According to the above method can be arbitrarily split into large transactions. If this transaction needs to be backed up to media, the "with truncate_only" option is not required. If you execute the "DUMP TRANSACTION with TRUNCATE_ONLY" command, you should first execute "dump database". And so on, we can split the table Delete, table insert and other large transactions.