We often get complaints from clients and their IT vendors about the out-of-control size of their SQL Server database transaction log file. In some rare cases log files have gotten so large that performing a backup of your database is nearly impossible.
In most cases log bloat can cause backups to run for longer than normal and sometimes for hours. This can result in the dangerous decision by admins to not perform backups at all. Avoiding backing up your data is never a correct solution to this problem. The root cause must be identified, corrected and backup plans formulated and maintained.
So what causes SQL Server log bloat?
SQL Server databases, by default, use 2 files on the server hard drive(s) to read and write data from the application. The data file (MDF file) for a Time Matters database will normally be named “TimeMatters[version#].mdf”. The transaction log file (LDF file) for a Time Matters database will normally be named “TimeMatters[version#]_log.ldf”. The version number, of course, varies according to your version of Time Matters. When Time Matters itself performs a backup of the database (as opposed to a SQL-generated backup), these files are combined into one single file, with a BAK extension. These two files (.MDF and .LDF) also exist for any other application that uses SQL Server as its database platform, and will be named according to the application.
When Time Matters creates the database during installation it creates it with a recovery model of “Bulk-Logged”, and in the past the databases normally had a recovery model of “Full”. The Recovery Model of a SQL Server database determines what data is stored in the transaction Log file.
There are 3 types of models;
- Full,
- Bulk-Logged
- Simple.
Full Mode stores all transactions in the log file. Bulk-Logged Mode stores normal transactions in full and bulk transactions minimally. Simple Mode only stores a transaction until it is committed to the database and stored in the MDF data file, at which point it is then removed from the log.
Log files are truncated whenever transaction log backups occur (not to be confused with a normal full backup), or when a “shrink” of the transaction log occurs manually. If a database is using Full or Bulk-Logged recovery mode and transaction log backups do not occur, the log will simply grow and grow and grow until a transaction log backup occurs or it is manually dealt with.
This is the main culprit of log bloat. Most firms do not backup transaction logs during the day, even though they should (I will address this in a later blog posting). Over time the transactions taking place inside Time Matters are stored in the log and remain there, increasing the size of the file and causing serious bloat.
If you are having trouble with time consuming backups or abnormal disk space use, then you may be experiencing log bloat. In Part 2 of this blog topic I will discuss “The Cure” for log bloat, and how to avoid recreating the problem in the future.
Need to do some maintenance on your out-of-control SQL data? Give us a call at 877-357-0555 or send an email to [email protected] to discuss your issue with one of our SQL experts.
Jack Schaller has been active in the field of law office technology since 1989, and has worked with a variety of commercial accounting, legal billing, practice management, and document management software products during his twenty plus years in the software consulting field. During his tenure as a software consultant he has garnered many sales and service awards for his work with legal software products. Jack is a frequent presenter at legal conferences and seminars, and is a regular contributor to TechnoLawyer and other technology publications.