Notes for truncation of transaction logs

Notes for truncation of transaction logs

When the database is used for a long time, logs also grow. When the log occupies a large amount of disk space, to save valuable disk space, it is often necessary to clear the log, I have also seen many log truncation Methods circulating on the Internet. In general, these methods are effective and feasible, but they ignore some key points and are easy for new users to make a big mistake!

First, let's talk about several parameters used by backup log...:
 With no_truncate

 With truncate_only

 With no_log

 1. With no_truncate is used to back up logs. With this option, MSSQL writes all active processes in the current database to the log. Therefore,No_truncate ensures that data is restored to the last update state.No_truncate is usually used in log backup after database system corruption.

 2. With truncate_only is not used to back up logs, but is used to explicitly cut off logs. With this option, MSSQL willWhen the inactive part (committed transactions) in is truncated, the physical space occupied by the truncated inactive transaction is replaced by the latest active log,This means that once a log is truncated, it will not be used for restoration.

 3. Similar to with truncate_only, with no_log is also used to explicitly cut logs. no_log is different from with truncate_only.The log truncation operation is not recorded in the log.

After talking about this, we will find two points:

1. explicitly truncation logs with truncate_only and with no_log have some risks, because "Once a log is truncated, it will not be used for restoration ". To remove the threat, it is recommended that you back up the log before explicitly truncating the log, perform a full backup after the log is truncated, and recreate the new log backup logic chain, it is used to prevent disconnection in log backup.

2. Use with truncate_only as much as possible. no_log is used only when the transaction log is fully filled. When the log is fully filled, with truncate_only cannot be used to truncate_only. This is because MSSQL will record this truncation, but there is no space left in the transaction log at this time, so it can only use no_log.

Summary:Do not use the explicit log truncation method. Log backup is often performed. After log backup, logs are also truncated.

To use the explicit truncation log, make sure to back up the log, especially the full backup after the truncation!

From: http://blog.csdn.net/suntt/archive/2006/02/26/610374.aspx