Log Backups, Auto Growth Settings, & Alerts (Part 1)

Why does missing a few log backups matter?

I was asked that question last week when one of our backup repositories was offline for a few hours. Aside from the impact to the databases’ recoverability (restore points), it shouldn’t matter. Right? Well, as with most things in the IT Universe: It Depends.

For most IT systems (file & application servers, appliances, etc.) backups should be taken to provide recovery points in the event of data loss or corruption. The interval between backups should be determined by the amount of data (or work time) that Management has determined it can afford to lose. These rules also apply to SQL databases. However, log backup interval requirements can also be influenced by other factors like: storage space availability and auto growth settings. Let’s look at how properly functioning log backups work, and how failing log backups can impact performance.

In a properly configured system, the log backup cycle should look like:

  • Transactional data is written to the log file.
  • Transaction log backup is created.
  • Committed (completed) transactions are removed from the log file.
  • Loop back to the beginning.

When the log backups are unable to complete successfully, the backup cycle looks like:

  • Transactional data is written to the log file.
  • Transaction log backup fails.
  • Transaction log file becomes full.
  • Processing of transactions is paused.
  • Log file is expanded (based on the database auto growth setting).
  • Expanded portion of the log file is zeroed.
  • Processing of transactions is resumed.
  • Loop back to the beginning.

Each time the log file needs to expand, the processing of transactions is temporarily paused, which impacts the database’s performance. Eventually, if log backups continue to fail, this cycle will result in the transaction log disk becoming full. At which point no new transactions can be written to the log, and the database becomes unavailable to the end users (i.e. it crashes).

So, failing/missing log backups can affect more than the database’s recoverability. They can also impact its performance and availability.

How much (and how often) the database’s performance is impacted by log file expansion events depends on what the database auto growth settings are, and how often transactions are being written. How long it takes for the disk to become full depends on how much free space the disk starts with, what the database(s) growth increments are, and how often transactions are being written. (Note: Transaction activity/rate is a key issue. Databases with low activity have little chance of running out of disk space. Databases with higher activity levels can go from normal to crisis very quickly.)

Now, let’s take a look at what the default growth settings are and how they can affect performance. Then we’ll look at some alerts to tell us when things are going wrong.

Auto Growth Settings
For SQL 2014 (and earlier) databases, the default auto growth settings for a new database are:

Data File: 1MB Initial Size / 1MB File Growth / Max File Size Unlimited
Log File: 1MB Initial Size / 10% File Growth / Max File Size Unlimited
DB AutoGrowth Settings

These settings are safe, but not optimal in most cases. The basic problem with these settings is the way they impact the database as it grows.

For the data file, the impact can be illustrated in the following chain of events:

  1. A new 1MB data file is created that contains no information. (ie. a 1MB data file containing 0MB of data)
  2. Data is written to the data until it reaches the file size. (ie. the 1MB data file now contains 1MB of data)
  3. The SQL server suspends normal operations to the database while the data file is grown by 1MB. (ie. the data file is now 2MB and contains 1MB of data) If Instant File Initialization (IFI) is enabled, the file is expanded and database operations resume. If IFI is not enabled, the expanded part of the data file must be zeroed before db operations resume, resulting in an additional delay.
  4.  Once the data file has been grown successfully, the server resumes normal database processing. At this point the server loops back to Step 2.

The server will continue this run-pause-run-pause processing until the data file reaches its Maxsize, or the disk becomes full. If the disk that the data file resides on has other files on it (ie. the C drive, or a disk that is shared by several databases), there will be other disk write events happening between the data file growth events. This may cause the data file expansion segments to be non-contiguous, increasing the file fragmentation and further decreasing the database performance.

For the log file, the impact is similar to the data file, with a few additional considerations:

  1. Instant File Initialization does not apply to log files. Log files are always zeroed to prevent data contamination when the transaction log is read during startup and recovery operations. So every time the log file grows the database will be paused while the expanded portion of the log file is zeroed.
  2. Percentage growth increments just magnify the run-pause-run-pause effect on small to medium sized databases. By using a 10% growth increment, the database will be paused at: 1MB, 1.1MB, 1.21MB, 1.33MB, 1.46MB, 1.6MB, 1.76MB, 1.93MB, 2.12MB, etc. (that’s 8 times the database will be paused before it reaches a 2MB log file size).
  3. Virtual Log File (VLF) numbers are greatly increased by small growth increments. High VLF numbers slow down the database during startup/recovery/backup operations, as each VLF must be read/checked.

Some good news is that beginning with SQL 2016 the default database size and growth settings have been improved.
Now the default database settings are:

Data File: 8MB Initial Size / 64MB File Growth / Unlimited Max File Size
Log File: 8MB Initial Size / 64MB File Growth / Unlimited Max File Size

These settings are much better than earlier versions, but are still not optimal for all databases. An 8MB initial size is a safe starting point, but it is very small when compared to the size of most commercial application databases.

Regardless of which version of SQL Server your database is on, there isn’t going to be an Auto Growth Setting that is optimal for all databases, and the optimal growth increment may change over time. So the ‘best’ option is to choose settings that are reasonable for each database, and periodically review them.

A process for choosing Auto Growth Settings:

  1. Talk to the database vendor/designer. Ask them for an estimated database size after 6 months of operation. This can be used for the data file’s initial size.
  2. Based on the initial data file size (or after monitoring the database), choose a growth increment that will only require the data file to expand periodically (i.e. every month or so).
  3. Monitor the transaction log. See how much data is being backed-up by the Transaction Log Backups. (Note: look at the log backups that occur immediately after the Index Maintenance jobs. Often these can be the biggest impact to the transaction log size in a given day/week)
  4. Once the volume of transaction log activity has been established, shrink the log, and then set the initial size to a reasonable value that is larger than the largest size the file grew to between log backups. This will set the transaction log to a size where is won’t need to grow during normal operations. (Note: only shrink and resize the transaction log during established maintenance periods, as these operations can significantly impact database performance)
  5. Periodically review the Transaction Log’s current size vs initial size. If significant growth has occurred, shrink and resize the log file, and possibly adjust the growth increment setting. (Resizing the log file will keep the VLF count from becoming excessively large.)

So, getting back to the original question: Why does missing a few log backups matter? 

The short answer is:
When transaction logs fail, the transaction log starts to grow. When the transaction log expands it impacts the database performance and can result in the database becoming unavailable (if it runs out of disk space). How the log grows, and how that will impact the database’s performance, is determined by the Auto Growth settings.

Next we’ll look at some alerts for when things start going wrong.

1 thought on “Log Backups, Auto Growth Settings, & Alerts (Part 1)”

Leave a Reply

Your email address will not be published.