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 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.
EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.
It’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans.