In the absence of SQL Transaction log backups, the database log file will continue to grow in size, unchecked

SUMMARY

In the absence of SQL Transaction log backups, the database log file will continue to grow in size, unchecked

ISSUE

Purpose

Explain the transactional log files' growth behavior and log space reuse (a.k.a. log truncation) with different SQL database models when using a Unitrends backup system.

Description

It is important to schedule regular transactional log backups when using any recovery model other than SIMPLE to control the growth of the transactional log file. If you do not run Unitrends transactional log backups, be sure the database recovery model is SIMPLE to prevent the transactional log files from growing unchecked.

RESOLUTION

In the SIMPLE recovery model

Truncation can take place immediately upon occurrence of a checkpoint operation. Pages in the data cache are first written to the log file and then flushed to disk. The space in any segment becomes inactive as a result and is made available for reuse. As a result, the space in inactive segments of the log is continually overwritten with new log records.

In the FULL recovery model

Once a SQL FULL backup of the database has been taken, the inactive segments of the log are no longer marked as reusable on checkpoints, so records in the inactive segments are retained alongside those in the active segments to maintain a complete, unbroken series of log records, which can be captured in log backups for use in point-in-time restore operations. Each time a SQL TRANSACTION LOG backup operation occurs, any unnecessary segments are marked as inactive and reusable. It is important to schedule transactional log backups of  of any database running in th FULL recovery model because this is the only operation that makes space available for reuse. In the absence of transactional log backups, the log file simply continues to grow in size, unchecked.

NOTE:  File level master backups will not truncate SQL transaction logs.

 

Recommendations for Bulk-logged Recovery Model

The SQL bulk-logged recovery model is used as a temporary recovery model to enhance performance when running bulk jobs. Unitrends does not support log backups while a database is in the bulk-logged recovery model because they are unnecessarily large (see Administrator Guide page 502). For compliance with Unitrends best practices, perform the following steps:

  1. Run a log backup while the database is still in full recovery model.
  2. Switch to the bulk-logged model.
  3. Perform the bulk operation. (For example, importing new labels, copying data from one table to another, or creating an index.)
  4. Switch back to the full recovery model.

From TechNet dd822915"So, while you can get performance improvements when switching to the BULK_LOGGED recovery model, you must consider changing to it as a temporary operation-just to improve your batch process and once the batch process is complete, you should switch back to FULL and perform a log backup as soon as possible."

From Microsoft SQL 2008 Documentation: "The bulk-logged recovery model is a special-purpose recovery model that should be used only intermittently to improve the performance of certain large-scale bulk operations, such as bulk imports of large amounts of data."

 

Synopsys

Unitrends VSS SQL agent: In the FULL Recovery Model, schedule a Full and Transaction Log backups.
Unitrends vProtect application-aware backup: Change every database model to SIMPLE.
Not Supported: BULKED LOGGED recovery model, as per Microsoft Best Practices.
 

Unitrends Related Articles
Administrator Guide for Recovery Series and Unitrends Backup 10.1
 

 

External Related Articles
Backup Under the Bulk-Logged Recovery Model
 

 

CAUSE

The reason for this relates to how and when space in the log is made available for reuse, a process known as log truncation. Any transactional log segment (Virtual Log File) of the log file that contains only inactive or committed log records can be truncated and that space reused by new transactions. If a segment contains even just a single log record that relates to an uncommitted transaction, or that is still required by some other database process such as replication, or contains log records that are more recent than the log record relating to the oldest open transactions or still required transaction, this segment is marked as active and can never be truncated.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Contact us