Switch to standard view 
  Sybase logo


About Transaction Logs

Most SQL Server processing is logged in the transaction log table, syslogs. Each database, including the system databases master, model, sybsystemprocs, and tempdb, has its own transaction log. As modifications to a database are logged, the transaction log continues to grow until it is truncated, either by a dump transaction command or automatically if the trunc log on chkpt option is turned on as described below. This option is not recommended in most production environments where transaction logs are needed for media failure recovery, because it does not save the information contained in the log.

The transaction log on SQL Server is a write-ahead log. After a transaction is committed, the log records for that transaction are guaranteed to have been written to disk. Changes to data pages may have been made in data cache but may not yet be reflected on disk.

This guarantee cannot be made when UNIX files are used as SYBASE devices.

Transaction Logs and commit transaction

When you issue a commit transaction, the transaction log pages are immediately written to disk to ensure recoverability of the transaction. The modified data pages in cache might not be written to disk until a checkpoint is issued by a user or SQL Server or periodically as the data cache buffer is needed by other SQL Server users. Note that pages modified in data cache can be written to disk prior to the transaction committing, but not before the corresponding log records have been written to disk. This happens if buffers in data cache containing dirty pages are needed to load in a new page.

Transaction Logs and the checkpoint Process

If the trunc log on chkpt option is set for a database, SQL Server truncates the transaction log for the database up to the page containing the oldest outstanding transaction when it issues a checkpoint in that database. A transaction is considered outstanding if it has not yet been committed or rolled back. A checkpoint command issued by a user does not cause truncation of the transaction log, even when the trunc log on chkpt option is set. Only implicit checkpoints performed automatically by SQL Server result in this truncation. These automatic checkpoints are performed using the internal SQL Server process called the checkpoint process.

The checkpoint process wakes up about every 60 seconds and cycles through every database to determine if it needs to perform a checkpoint. This determination is based on the recovery interval configuration parameter and the number of rows added to the log since the last checkpoint. Only those rows associated with committed transactions are considered in this calculation.

If the trunc log on chkpt option is set, the checkpoint process attempts to truncate the log every sixty seconds, regardless of the recovery interval or the number of log records. If nothing will be gained from this truncation, it is not done.

Transaction Logs and the recovery interval

The recovery interval is a configuration parameter that defines the amount of time for the recovery of a single database. If the activity in the database is such that recovery would take longer than the recovery interval, the SQL Server checkpoint process issues a checkpoint. Because the checkpoint process only examines a particular database every 60 seconds, enough logged activity can occur during this interval that the actual recovery time required exceeds the time specified in the recovery interval parameter.

Note that the transaction log of the tempdb database is automatically truncated during every cycle of the checkpoint process, or about every 60 seconds. This occurs whether the trunc log on chkpt option is set on tempdb or not.

Turning Off Transaction Logging

Transaction logging performed by SQL Server cannot be turned off, to ensure the recoverability of all transactions performed on SQL Server. Any SQL statement or set of statements that modifies data is a transaction and is logged. You can, however, limit the amount of logging performed for some specific operations, such as bulk copying data into a database using bulk copy (bcp) in the fast mode, performing a select/into query, or truncating the log. See the Tools and Connectivity Troubleshooting Guide and the SQL Server Reference Manual for more information on bcp. These minimally logged operations cause the transaction log to get out of sync with the data in a database, which makes the transaction log useless for media recovery.

Once a non-logged operation has been performed, the transaction log cannot be dumped to a device, but it can still be truncated. You must do a dump database to create a new point of synchronization between the database and the transaction log to allow the log to be dumped to device.

What Information Is Logged

When a transaction is committed, SQL Server logs every piece of information relating to the transaction in the transaction log to ensure its recoverability. The amount of data logged for a single transaction depends on the number of indexes affected, the amount of data changed, and the number of pages that must be allocated or deallocated. Certain other page management information may also be logged. For example, when a single row is updated, the following types of records may be placed in the transaction log:

Sizing the Transaction Log

There is no hard and fast rule dictating how big a transaction log should be. For new databases, a log size of about 20 percent of the overall database size is a good starting point. The actual size required depends on how the database is being used; for example:

Because there are many factors involved in transaction logging, you usually cannot accurately determine in advance how much log space a particular database requires. The best way to estimate this size is to simulate the production environment as closely as possible in a test. This includes running the applications with the same number of users as will be using the database in production.

Separating Data and Log Segments

Always store transaction logs on a separate database device and segment from the actual data. If the data and log are on the same segment, you cannot save transaction log dumps. Up-to-date recovery after a media failure is therefore not possible. If the device is mirrored, however, you may be able to recover from a hardware failure. Refer to the System Administration Guide for more information.

Also, the data and log segments must be on separate segments so that you can determine the amount of log space used. dbcc checktable on syslogs only reports the amount of log space used and what percentage of the log is full if the log is on its own segment.

Finally, because the transaction log is appended each time the database is modified, it is accessed frequently. You can increase performance for logged operations by placing the log and data segments on different physical devices, such as different disks and controllers. This divides the I/O requests for a database between two devices.

Truncating the Transaction Log

The transaction log must be truncated periodically to prevent it from filling up. You can do this either by enabling the trunc log on chkpt option or by regularly executing the dump transaction command.

Up-to-the-minute recoverability is not guaranteed on systems when the trunc log on chkpt option is used. If you use this on production systems and a problem occurs, you will only be able to recover up to your last database dump.
Because the trunc log on chkpt option causes the equivalent of the dump transaction with truncate_only command to be executed, it truncates the log without saving it to a device. Use this option only on databases for which transaction log dumps are not being saved to recover from a media failure, usually only development systems.

Even if this option is enabled, you might have to execute explicit dump transaction commands to prevent the log from filling during peak loads.

If you are in a production environment and using dump transaction to truncate the log, space the commands so that no process ever receives an 1105 (out of log space) error.

When you execute a dump transaction, transactions completed prior to the oldest outstanding transaction are truncated from the log, unless they are on the same log page as the last outstanding transaction. All transactions since the earliest outstanding transaction are considered active, even if they have completed, and are not truncated.

Figure 1 illustrates active and outstanding transactions:

Figure: Active transactions and outstanding transactions illustrated
This figure shows that all transactions after an outstanding transaction are considered active. Note that the page numbers do not necessarily increase over time.

Because the dump transaction command only truncates the inactive portion of the log, you should not allow stranded transactions to exist for a long time. For example, suppose a user issues a begin transaction command and never commits the transaction. Nothing logged after the begin transaction can be purged out of the log until one of the following occurs:

Stranded transactions are usually due to application problems but can also occur as a result of operating system or SQL Server errors. See, "Managing Large Transactions", below, for more information.

Identifying Stranded Transactions with syslogshold

In SQL Server release 11.0 and later, you can query the syslogshold system table to determine the oldest active transaction in each database. syslogshold resides in the master database, and each row in the table represents either:

A database may have no rows in syslogshold, a row representing one of the above, or two rows representing both of the above. For information about how Replication Sever truncation points affects the truncation of a database's transaction log, see your Replication Server documentation.

Querying syslogshold can help you when the transaction log becomes too full, even with frequent log dumps. The dump transaction command truncates the log by removing all pages from the beginning of the log up to the page that precedes the page containing an uncommitted transaction record (the oldest active transaction). The longer this active transaction remains uncommitted, the less space is available in the transaction log, since dump transaction cannot truncate additional pages.

For information about how to query syslogshold to determine the oldest active transaction that is holding up your transaction dumps, see Backing Up and Restoring User Databases in the System Administration Guide.

Managing Large Transactions

Because of the amount of data SQL Server logs, it is important to manage large transactions efficiently. Four common transaction types can result in extensive logging:

The following sections contain explanations of how to use these transactions so that they do not cause extensive logging.

Mass Updates

The following SQL statement updates every row in the large_tab table. All of these individual updates are part of the same single transaction.

1> update large_tab set col_1 = 0
2> go
On a large table, this query results in extensive logging, often filling up the transaction log before completing. In this case, an 1105 error (transaction log full) results. The portion of the transaction that was processed is rolled back, which can also require significant server resources.

Another disadvantage of unnecessarily large transactions is the number or type of locks held. An exclusive table lock is normally acquired for a mass update, which prevents all other users from modifying the table during the update. This may cause deadlocks.

You can sometimes avoid this situation by breaking up large transactions into several smaller ones and executing a dump transaction between the different parts. For example, the single update statement above could be broken into two or more pieces as follows:

1> update large_tab set col1 = 0
2> where col2 < x 
3> go
1> dump transaction database_name 
2> with truncate_only  
3> go
1> update large_tab set col1 = 0 
2> where col2 >= x 
3> go
1> dump transaction database_name 
2> with truncate only
3> go
This example assumes that about half the rows in the table meet the condition col2 < x and the remaining rows meet the condition col2 >= x.

If transaction logs are saved for media failure recovery, the log should be dumped to a device and the with truncate_only option should not be used. Once you execute a dump transaction with truncate_only, you must dump the database before you can dump the transaction log to a device.

Delete Table

The following SQL statement deletes the contents of the large_tab table within a single transaction and logs the complete before-image of every row in the transaction log:

1> delete table large_tab 
2> go
If this transaction fails before completing, SQL Server can roll back the transaction and leave the table as it was before the delete. Usually, however, you do not need to provide for the recovery of a delete table operation. If the operation fails halfway through, you can simply repeat it and the result is the same. Therefore, the logging done by an unqualified delete table statement may not always be needed.

You can use the truncate table command to accomplish the same thing without the extensive logging:

1> truncate table large_tab 
2> go
This command also deletes the contents of the table, but it logs only space deallocation operations, not the complete before- image of every row.

Insert Based on a Subquery

The SQL statement below reads every row in the large_tab table and inserts the value of columns col1 and col2 into new_tab, all within a single transaction:

1> insert new_tab select col1, col2 from 
2> go
Each insert operation is logged, and the records remain in the transaction log until the entire statement has completed. Also, any locks required to process the inserts remain in place until the transaction is committed or rolled back. This type of operation may fill the transaction log or result in deadlock problems if other queries are attempting to access new_tab. Again, you can often solve the problem by breaking up the statement into several statements that accomplish the same logical task. For example:

1> insert new_tab 
2> select col1, col2 from large_tab where col1 
<= y 
3> go
1> dump transaction database_name 
2> with truncate_only
3> go
1> insert new_tab 
2> select col1, col2 from large_tab where col1 
> y 
3> go
1> dump transaction database_name 
2> with truncate_only
3> go

This is just one example of several possible ways to break up a query.
This approach assumes that y represents a median value for col1. It also assumes that null values are not allowed in col1. The inserts run significantly faster if a clustered index exists on large_tab.col1, although it is not required.

If transaction logs are saved for media failure recovery, the log should be dumped to a device and the with truncate_only option should not be used. Once you execute a dump transaction with truncate_only, you must dump the database before you can dump the transaction log to a device.

Bulk Copy

You can break up large transactions when using bcp to bulk copy data into a database. If you use bcp without specifying a batch size, the entire operation is performed as a single logical transaction. Even if another user process does a dump transaction command, the log records associated with the bulk copy operation remain in the log until the entire operation completes and another dump transaction command is performed. This is one of the most common causes of the 1105 error. You can avoid it by breaking up the bulk copy operation into batches. Use this procedure to ensure recoverability:

  1. Turn on the trunc log on chkpt option:
    1> use master 
    2> go
    1> sp_dboption database_name,
    2> trunc, true
    3> go
    1> use database_name 
    2> go
    1> checkpoint
    2> go

    "trunc" is an abbreviated version of the option trunc log on chkpt.
  2. Specify the batch size on the bcp command line. This example copies rows into the pubs2.authors table in batches of 100:
    Platform Command
    UNIX bcp -b 100
    Digital OpenVMS bcp /batch_size=100
    Novell NetWare load bcp pubs2.authors in phonebook -b 100
    OS/2 bcp pubs2.authors in phonebook -b 100
    Windows NT bcp pubs2.authors in phonebook -b 100
    SCO UNIX bcp pubs2.authors in phonebook -b 100
  3. Turn off the trunc log on chkpt option when the bcp operations are complete, and dump the database.
In this example, a batch size of 100 rows is specified, resulting in one transaction per 100 rows copied. You may also need to break the bcp input file into two or more separate files and execute a dump transaction between the copying of each file to prevent the transaction log from filling up.

If the bcp in operation is performed in the fast mode (with no indexes or triggers), the operation is not logged. In other words, only the space allocations are logged, not the complete table. The transaction log cannot be dumped to a device in this case until after a database dump is performed (for recoverability).

If your log is too small to accommodate the amount of data being copied in, you may want to do batching and have the sp_dboption trunc log on checkpoint set. This will truncate the log after each checkpoint.

See the Using bcp to Transfer Data to and from SQL Server in the Utility Programs for UNIX manual.

Back to Top
© Copyright 2010, Sybase Inc.