877-375-2810 [email protected]

Out-of-Control SQL Database Log File Part 2: The Cure

by | Jul 15, 2014 | Uncategorized

In Part 1 of this topic we discussed the culprit of SQL Server log bloat, the symptoms and issues that it may cause and discussed the different recovery modes of SQL Server databases.  After reading Part 1 you may have found that your firm is experiencing log bloat according to the symptoms listed. In Part 2 we will discuss the solution for this problem and the steps to take to ensure that it doesn’t happen in the future.

To review the discussion in Part 1, SQL Server log bloat is caused by transactions from Time Matters, or another application or process, that get stored in the transaction log when the database is using Full or Bulk-Logged recovery mode and the transaction logs are not backed up regularly. The transactions remain in the log for an indefinite amount of time, and the log continues to grow.  This produces overuse of disk space and can cause backups to run for long periods of time, or make it near impossible to perform any backup at all.

The best way to cure this problem, in my professional opinion, is to perform transaction log backups at a minimum interval of 1 hour. Transaction log backup plans backup only the transactions that have occurred since the last backup of any type (full backup, differential or log). The Log file is then truncated (transactions removed) once it is backed up. This keeps the log file size to a minimum and also provides a firm with more options for disaster recovery. All backup plans should be designed and implemented by a firm administrator, Consultant, or IT vendor who has an advanced understanding of Time Matters and SQL Server. In a later blog posting I will discuss the following:

      • why firms should choose to perform transaction log backups,
      • the benefits of doing so,
      • how long the backups should be kept
      • how it can save users a lot of pain in a true disaster.

For a firm that does not wish to perform transaction log backups, a few steps can be taken by a firm administrator, Consultant, or IT vendor with an advanced understanding of SQL Server.

Below are the steps to follow for those administrators, consultants, and IT professionals who are comfortable performing this type of procedure:

Steps To Reduce A SQL Server Log File

Things to be aware of:

  • This method uses the “Shrink File” option for SQL Server Databases
  • Shrinking files or databases should ONLY be performed for specific situations, and not often, as it can result in record fragmentation.
  • A shrink cannot occur during a backup, and a backup cannot occur during a shrink operation.
  • THIS TASK SHOULD ONLY BE PERFORMED BY A KNOWLEDGEABLE SQL PROFESSIONAL.

Steps to perform :

  1. If you are able, take a backup of your database via Time Matters, SQL Studio, or another backup utility. If you are unable to take a backup due to available disk space or database size, contact your IT vendor and ask for assistance.
  2. Using SQL Studio, Change the Time Matters database to Simple Recovery Mode under Options in Properties. This will ensure that the log does not bloat in the future.
  3. Under Properties>Tasks>Shrink>File, choose Log file type and make sure the option to release unused space (to the hard drive) is chosen. This will shrink the file to its minimum allocated size.

ELS can helpwith your bloated SQL Server log file.  If you need assistance with a transaction log backup plan, or reducing the size of your SQL log file, or any other SQL-related issue, please give us a call at 877-357-0555 or email us at [email protected].  We will be glad to help!