How to deal with unexpectedly large transaction log files

A common problem faced by SQL server administrators is the unexpectedly large size to which transaction log files often grow. Large transaction log files pose many problems to the server itself such as sluggish performance, threat of data corruption, server downtime and server inaccessibility. It thus becomes a priority to deal with such huge files at the earliest before they can cause hindrances in the normal working of the server.

In this article we’ll be focusing on this issue and detailing the causes behind this problem, ways to prevent it from happening (if possible) and tips to resolve it.

Read How to find sql transactions with large logs  for techniques on finding queries generating large transaction logs

When can transaction logs grow too big?

There could be many causes behind an overgrown transaction log, but they can be categorized broadly under 2 spectrums – opting for Full Recovery but not taking regular backups, and endlessly running transactions. Let’s go through both briefly.

1. Full Recovery Mode and lack of proper Backups

This is probably an error most people facing this issue have committed. There are 3 recovery models in SQL server namely, Simple, Full and Bulk-Logged. While Bulk-Logged recovery mode is a sort of hybrid model used by experienced people, most users stick to either the Simple or the Full recovery model.

Simple Recovery Model

Under this model, transaction log files are primarily used for crash and restart recovery and once they’ve satisfied that purpose, they are reused or other tasks. As a result, SQL server only keeps the information it needs for crash/restart recovery. Once the SQL server makes sure that the main purpose of the file has been achieved, the log is marked for truncation making it available for re-use.

Full Recovery Model

Under this model, a user can restore to a specific point in time (like a System Restore) that is covered by a log backup or just to a specific point as long as the file is available. As a result, the SQL server NEVER truncates the log file and instead, allows it to grow until you take a backup or run out of space on the hard drive. Although to start using the Full recovery model it is mandatory for you to take a full initial backup else you can’t make the switch to this mode at all, beyond the first step, you’ll just have to remember to backup regularly to prevent the log file from expanding to a mammoth size.

Thus actually, being in Full recovery mode and not taking backups is a major trigger for this problem.

So what should be the ideal backup frequency?

Evaluate it this way – how soon do you expect your log file to overflow (this will depend on the number of transactions taking place on your server)? If it comes to 1 day, then you should take a backup every day. That also means that if it comes to 10 minutes, you should take a backup every 10 minutes.

2. Endlessly running transactions

If a transaction running on the server takes a long time or does a lot of changes, the log file cannot truncate on checkpoint for any of the changes that started since that transaction started. This means that performing a big delete (deleting millions of rows in one delete statement) is one transaction and the log cannot do any truncating until that whole delete is done. End result – the log file while continue to grow till the transaction completes. And if a corruption occurs in the oversized log file before that happens it could lead to a horrible deadlock situation.

So how can this be avoided?

Set an optimum size as the maximum size for your log file keeping the worst case scenarios and known long operations in mind. Don’t start long conversations with the SQL server and watch implied transactions in DML statements. This includes transactions that perform automatic commits when done. If undertaking operations on multiple rows, consider batching those operations into more manageable chunks and giving the log time to recover.

Dealing with oversized transactions logs

Almost all administrators resort to the ultimate step of shrinking or truncating the log files to deal with their huge size. It can be done easily with a variety of options and works in most situations. However, it can often backfire terribly. So the trick lies in shrinking the log file smartly. Here are the steps that you should ideally follow:

1. Take a full database backup
2. Make sure that the database is in Full recovery mode
3. Make sure you have regular log backups running
4. Come up with a practical size for your log file based on your system requirements
5. Shrink the log file with the chosen transaction file size

If nothing seems to work, try opting for SQL database repair tool through professional company. Such products though charge you, often prove to be worthy.

Avoid doing these

In an attempt to resolve the issue with workarounds, DO NOT fall prey to using these techniques:

• Backing up the log with TRUNCATE_ONLY option and then executing SHRINKFILE.
• Shrinking the log file to an extremely small size for e.g, 1 MB
• Detach the database, delete the log file, and re-attach (Extremely dangerous)
• Use the "shrink database" option
• Create a second log file

To sum it up

Shedding weight from an overburdened log file only to have it bloat up again would be futile. Read up more and don’t resort to blind log file shrinking so that you don’t have to ultimately resort to SQL database repair.

Author Bio: Priyanka Chouhan is a technical writer in Stellar Data Recovery with 5 years of experience and has written several articles on SQL server & SharePoint. In the spear time she loves reading and gardening

Read More on Transaction Logs

SQL Server - Preallocate SQL Transaction Logs for large queries ...

Manage Transaction Logs during Index Rebuild - SQL Server DBA

Modify sql transaction log file size - increase or decrease - SQL ...