The Accidental DBA: Transaction Log for Backups Need Proper Management

By | In Blog, Database Administration | January 28th, 2015

Accidental DBATransaction 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.

Conclusion

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.

Contact Us

Leave a Reply

Your email address will not be published.
Required fields are marked (*).