Learn How and When to Shrink Transaction Log File

Transaction logs consist of the records regarding activities taking place on the server database to which they are associated. At one point of time, these files run out of memory to record any more logs. Therefore, it is necessary to backup logs so that they can be cleared off whenever required. Shrinking is of the many procedures used for managing the transaction log files. The following segment acts as a guide on when to shrink transaction log files and what are the best practices to adopt when you do so.

Whys and Wherefores of Shrinking Transaction Log

When transactions are concerned, there are going to be instances where there is lack of storage space. If a transaction file consists of unused space it can be regained for future use. Doing this reduces the unnecessarily growing size of a transaction log file. What is being done here is a procedure known as shrinking of the log file.

NOTE: It is only possible to shrink a transaction log file in a scenario where the database is in an online state and a virtual log is free. However, in certain cases until the next truncation a log cannot be shrunk.

Generally, the truncation process occurs on its own as part of the simple recovery model when a database it is associated to is backed up. In addition, it also occurs as part of the full recovery model where the transaction is backed up. Nevertheless, a number of factors can affect the truncation procedure and delay it.

How Does Shrinking Take Place?

The procedure of shrinking a transaction log file is nothing but the removal of one or more than one virtual logs that exist. The unit of the reduction in size is equal to a virtual log file.

This can better be understood with an example:

There is a log file of 600 MB that is divided into multiple portions, i.e. 100 MB of virtual log file each, which makes 6 of them. This is done because the size of a log can only be decreased with an increment of up to 100 MB each. The variation in sizes can be 500 or 600 MB and not 533 or 625 MB.

Any virtual log that consists of active log records, i.e. active virtual log – is a part of the logical log file. Therefore, such virtual logs cannot be removed.

Things to Remember

In order to performing the shrinking of log files, it is necessary to first monitor the log space then a sequence of two stages follow up, i.e. shrinking of log file and then monitoring the shrinking events that have taken place.

To monitor available log space:

  1. DBCC SQLPERF (Transact-SQL)
  2. Sys.database files (Transact-SQL) – Lets you see the – size, growth, and max_size columns in association to the log

To shrink a transaction log:

  2. Shrink transaction log file via SSMS

To monitor shrinking events:

  • Event Class – Log File Auto Shrink must be used

When a transaction log is shrunk removal of virtual logs (inactive) takes place. Therefore, in order to remain on the safer side and ensure database integrity during the procedure, it is highly recommended that backups be maintained.

Tips for SQL Server Transaction Log Backup

  1. In order to truncate SQL Server transaction log files, you must have them backed up. Not having regular backups of the transaction log may result in filling up the disk space.
  2. A full backup is not recommended, as it does not include the transaction logs.
  3. Use of ‘BACKUP LOG’ must be made in order to backup transaction logs.
  4. If transaction logs are not to be backed up according to the Database Administrator, Simple Recovery is suggested.

Leave a Reply

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