Handling the completely filled Transaction Log problem

Is your Transaction Log full ? And are you not able to execute transactions because of error saying "Transaction Log is full" ?

Problem Explanation:

In SQL Server, we have three recovery models.

1. Simple Recovery Model
2. Full Recovery Model
3. Bulk-Logged Recovery Model.

An SQL Server database has two files by default namely,

• Data file “.mdf”
• Log file “.ldf”

This database should be managed under one of the earlier mentioned recovery models.

NOTE: Taking Transaction Log Backup is the only way to truncate(empty) the log file.

In Simple Recovery model,
SQL Server automatically truncates the log file in regular intervals.
That is the reason why we are not allowed to take a transaction log backup in this mode of recovery (And there is no need to take transaction log backup also in this mode).
So, coming to the main point, log file will never get fulled in this mode of recovery.

In Full Recovery model,
Burden of maintaining and controlling the transaction log is completely left on the DBA.
So, If we dont take regular log backups of the database, then log file will get filled up until the threshold (Refer the NOTE mentioned earlier).

Listed below are some other regular scenarios when transaction log file of SQL Server database grows:

• If there are no scheduled log backups or log backups are not working.
• If there are active transactions in the log file
• If there are long running queries
• In database mirroring if mirror server fails
• In replication if distributor fails
• If there is no disk space to grow the Transaction Log file
• If bulk data was imported without dividing into multiple batches or transactions.

Problem Solution:

When the transaction log becomes full, SQL Server Database Engine issues a 9002 error.

Follow the below mentioned steps to take a transaction log backup.

1. After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

2. Expand Databases, and select a desired database.

3. Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.

4. In the Database list box, verify the database name. You can optionally select a different database from the list.

5. Verify that the recovery model is either FULL or BULK_LOGGED.

6. In the Backup type list box, select Transaction Log.

7. Optionally, you can select Copy Only Backup to create a copy-only backup. Leave this here.

8. Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.

9. Optionally, in the Description text box, enter a description of the backup set.

10. Choose the type of backup destination by clicking Disk or Tape. To select the paths of up to 64 disk or tape drives containing a single media set, click Add. The selected paths are displayed in the Backup to list box.

11. To remove a backup destination, select it and click Remove. To view the contents of a backup destination, select it and click Contents.

12. To view or select the advanced options, click Options in the Select a page pane.

13. Select an Overwrite Media option, by clicking one of the following:

• Back up to the existing media set
  • For this option, click either Append to the existing backup set or Overwrite all existing backup sets.
  • Optionally, select Check media set name and backup set expiration to cause the backup operation to verify the date and time at which the media set and backup set expire.
  • Optionally, enter a name in the Media set name text box. If no name is specified, a media set with a blank name is created. If you specify a media set name, the media (tape or disk) is checked to see whether the actual name matches the name you enter here.
  • If you leave the media name blank and check the box to check it against the media, success will equal the media name on the media also being blank.

• Back up to a new media set, and erase all existing backup sets
  • For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box.

14. In the Reliability section, optionally, check:

• Verify backup when finished.
• Perform checksum before writing to media, and, optionally, Continue on checksum error.

15. In the Transaction log section:

• For routine log backups, keep the default selection, Truncate the transaction log by removing inactive entries.
• To back up the tail of the log (that is, the active log), check Back up the tail of the log, and leave database in the restoring state.

16. If you are backing up to a tape drive (as specified in the Destination section of the General page), the Unload the tape after backup option is active. Clicking this option activates the Rewind the tape before unloading option.

17. SQL Server 2008 Enterprise and later supports backup compression. By default, whether a backup is compressed depends on the value of the backup-compression default server configuration option. However, regardless of the current server-level default, you can compress a backup by checking Compress backup, and you can prevent compression by checking Do not compress backup.

After taking transaction log backups, in-active log will be truncated and empty space is created for the upcoming transactions. We can come out of the problem.

In order to prevent the occurrence of this problem in future, log backups should be scheduled at regular intervals according to the server traffic when we have opted for the Full Recovery mode.

Note: The process detailed above will only empty the log file. It will not reduce the log file size.The side reduction of database files is achieved by shrinking them. Shrinking is not a suggested operation. It is always good to maintain adequate log size by taking regular backups.