Transaction log for database [...] is full Error

Owned by Shelley Crouse
Last updated: Jul 17, 2017 by Jacob Turley
3 min read

Problem

When running RTA you get an error that, The transaction log for database '...' is full, for the RTA database. This error is more likely to happen when running a Special Utility Routine / Rebuild Utility in RTA, as those routines generate significantly more data transactions.

Cause

No matter what operation you are doing in RTA, your database log file is out of room for one of 3 reasons:

  1. Your log is set to autogrow, but can't because your SQL Server has insufficient disk space to grow the log.
  2. Your log is not set to autogrow, you are running in Full recovery mode, and you have not performed a transaction log backup recently.
  3. Your log is not set to autogrow, you are running in Simple or Bulk Logged recovery mode, and the log file isn't big enough to handle the amount of transactions between log commits.

Solution

First, identify what your log growth setting is set to. If set to auto-grow, first check to make sure you have sufficient disk space. If disk space isn't available, perform necessary actions to clean up old files and free up disk space.

Next, you should check to make sure that your log is being properly maintained.

Check your database recovery model.

If your recovery model is set to full, make sure that you have regularly scheduled log backups taking place. RTA recommends performing a full backup at least once per day, and a log backup at least twice per day. More frequent log backups may be necessary depending on your aversion to data loss or the availability of disk space on the server. 

If your recovery model is set to simple, no log backups are required, and no further troubleshooting is required.

Follow the steps below to verify the database is set to "auto-grow", otherwise check to make sure you have sufficient disk space.

  • Go into SQL Server Management Studio 

  • Right click on your Database and go to Properties -

  • Click on Files.

  • Define/edit the Autogrowth settings by clicking on the ellipses buttons circled in red as shown below.

  • Make selections according to your needs.

  • Enable Autogrowth – Checking this box allows this database to auto-grow.
  • The File Growth option is used to set how a database file will grow when it runs out of space.  This can be set to grow by percentage or in megabytes.   Allowing it to grow in Megabytes helps keep them in a uniform fixed size, otherwise as the database gets bigger, so will the autogrowth amount.
  • The Maximum File Size option allows you to set the maximum size that SQL Server will allow the file to grow.    The Unlimited/Unrestricted growth option minimizes application failures due to auto-growth restrictions.  In order to not let one database use up all your disk space then setting a maximum file size would be a way to prevent this.