Show TOC

Log TruncationLocate this document in the navigation structure

Use

Log truncation is a SQL Server feature that enables disk space reserved for logs to be re-used. The following section gives you some background information on how SQL Server logs are structured and how the truncating mechanism works.

Transaction Log Structure

Each SQL Server database has its own transaction log. The transaction log consists of one or more log files. A log file contains log records including undo and redo information. The undo information is necessary for rolling back open database transactions. The redo information is needed for recovery. The transaction log of SQL Server is comparable with the rollback segment plus the redo logs and archive logs of other database products.

If a transaction log is full, the SQL Server comes to a standstill. When the Full Recovery Model is enabled, transaction log backups must be performed regularly. After each backup, the log is automatically truncated. This frees space in the transaction log without changing the size of the log files.

Note

Ideally the size of the log files never changes.

You can set the autogrow option for the log files to ensure that they do not fill up. This is particularly important when an abnormal amount of space is temporarily required, for example, when transaction log backups can temporarily not be performed. A backup that is executed after the log file has grown abnormally does not automatically decrease the size of the files. To decrease the size, you have to shrink them manually. For details refer to SQL Server Books Online.

The following graphic illustrates the structure of a log file.

Each log file consists of several virtual log files (VLF). These are managed internally by SQL Server therefore you cannot define their number or size. They are not visible in the SQL Server Management Studio.

The virtual log files are sequentially filled with log records. A VLF is called an active VLF if it contains log records.

Truncation

The active part of the log contains all log records, which are newer than the oldest open transaction. When the log is truncated, the VLFs, which do not contain the active part of the log, are released for re-use. The active part of the log is left unchanged, it is not truncated. Truncation only frees whole virtual log files for re-use and not parts of them.

Log files after Truncation

For more information on Transaction Log architecture, see also the SQL Server Books Online.

Full Transaction Logs

Even though the truncation process prevents log files from filling up in many cases, there are still many reasons why logs might fill up and bring the system to a standstill:

  • The transaction log might be too small
  • The frequency of the transaction log backups might be too low
  • A huge open transaction, for example a SAP client copy, might fill up the transaction log
  • An old open transaction might prevent the truncation of the log
    Note

    If a transaction remains uncommitted for a number of days, the active part of the transaction log grows continuously and truncation does not decrease the amount of space used.

    You can determine the oldest open transaction with the SQL commandDBCC OPENTRAN(<SAPSID>)