Select Page

The Accidental DBA: Transaction Log for Backups Need Proper Management

Joel Beam | | January 28, 2015

Transaction logs are a vital but frequently mismanaged component of the SQL Server Database environment.

Transaction log issues are a challenge most of our clients face at some point. The problem occurs when transaction log files for backups are not managed properly, leading to a hard drive filling up or other issues. The problem is very common, but transaction logs can be easily pruned and problems prevented.

As we explained in a prior post about becoming an “Accidental DBA”, it’s tough when you’re a new database administrator to get up and running when you’re unsure where to start and what to do. We provided some basics for the Accidental DBA, but what happens when you reach an impasse such as mismanaged transaction logs?

Transaction Logs Pile Up

As Microsoft explains, when a transaction log becomes full, the SQL Server Database Engine returns a 9002 error. If this error message is not immediately attended to, all future transactions in that database, and potentially the entire application, could be halted. When there are other databases dependent on this particular server and drive, everything could grind to an abrupt halt.

Preventing Transaction Log Problems

The possible resolutions for a full transaction log, according to Microsoft, include:

  • Backing up the log file;
  • Freeing disk space, allowing the log to automatically grow;
  • Moving the log file to a hard drive on which there is sufficient space;
  • Increasing the log file size;
  • Adding a log file on a different disk; or
  • Completing or killing a running database transaction.

A transaction log may fill when the database is online or when in recovery. How this occurs dictates the response to the full log file. To gather more information, use log reuse wait and log reuse wait desc columns in the sys.database catalog view.

One issue that can cause problems is the backup of the transaction log file. If, for example, the transaction log has not been backed up recently, the log may be truncated. Microsoft explains:

If the log has never been backed up, you must create two log backups to permit the Database Engine to truncate the log to the point of the last backup. Truncating the log frees space for new log records. To keep the log from filling up again, take log backups frequently.

If you are able to delete or move other files, this can create enough space that will allow the system to automatically increase the log file size. Otherwise, you may wish to move the log file to another disk drive to allow the log to grow.

Another remedy can be increasing the log file size. This works only if there is sufficient space on the disk. The maximum log file size is two terabytes per file. If you have the autogrow option disabled, the file size can be manually increased. If the size limit has been reached, increase the MAXSIZE value.

It is also simple to add a new log file on a disk with adequate space using the command ALTER DATABASE <database_name> ADD LOG FILE.

Keeping Transaction Logs Tidy

If you don’t take the proper steps to regularly purge and clean up database log files, these files will continue to grow until they create problems. The task needs to be completed periodically to ensure the database continues operating optimally and efficiently.


If this post did not answer all your transaction log questions, feel free to contact a Datavail expert. Additionally, check out our other Accidental DBA posts for common questions and troubleshooting.

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

ORA-12154: TNS:could not resolve the connect identifier specified

Most people will encounter this error when their application tries to connect to an Oracle database service, but it can also be raised by one database instance trying to connect to another database service via a database link.

Jeremiah Wilton | March 4, 2009

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

12c Upgrade Bug with SQL Tuning Advisor

Learn the steps to take on your Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.