Accidental DBA Tip – Manage Transaction Logs
Transaction logs are a key utility for managing the recovery and performance of your database. In most engines, a transaction log maintains a copy of the data being written to your database to assure the integrity of the data. If you want to do more research about what I mean about the Integrity of the data, look up the acronym ACID.
The problem with a transaction log is that it can become full. In the old days, when that happened, your database shut down and would not allow additional activity until the database was backed up and the transaction log reduced. Today, transaction logs automatically grow (by default) making room for additional transaction activity. This results in your database remaining online, but incremental reduction in performance as the log file(s) is expanded.
The key is to do one of two things. If your database recovery model is set to simple, the transaction log is still utilized in order to assure ACID integrity of your data. The global setting for a CheckPoint for the instance of SQL Server hosting the database determines how long completed transactions remain in the transaction log before they can be dropped. This value is demonstrated as the “Recovery Interval” in SQL Server 2008 server properties on the database settings page. In this case, you need to set the recovery interval to a setting that will purge your simple transaction logs without requiring them to expand on demand.
The second thing you need to do is to handle transaction logs for databases that are set up for Full or Bulk- logged options on the Database Properties – Options page. With these backup configurations, the only way to clear and re-use the transaction log is to perform a database backup or a transaction log backup. At that point, the transactions in the transaction log which have been committed by backup may be deleted from the file, and that space may be re-used by new transactions.
In short, your database backup system is critical to the ongoing performance of a SQL Server database due to the transactional capabilities built into the server. Many accidental DBA has fallen prey to this problem.
That’s my Accidental DBA Tip for now. If you need further tutorials to get things rolling there is plenty of good training online, or find yourself a good administrators manual. If you have any favorite blogs, sites, books or online training you’d like to recommend, drop me a note at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Working With Third Party Vendors
As a DBA working with third party vendors can be difficult at times. I will share some of my tips to make it a little bit easier.
Featured White Paper(s)
THE SQL SERVER SECURITY THREAT — IT’S CLOSER THAN YOU THINK
Written by: Kevin Beaver Kevin Beaver, CISSP, is an independent information security consultant, author, expert witness an… (read more)