Log records that are not managed correctly will eventually fill up the disk causing no more modifications to the database. Transaction log growth can occur for a few different reasons. Long running transactions, incorrect recovery model configuration and lack of log backups can grow the log.

Log truncation frees up space in the log file so the transaction log can reuse it. Unless there is some kind of unexpected delay, log truncation will occur automatically after a checkpoint (if the database is in SIMPLE recovery model) or after a log backup (if the database is in FULL or BULK-LOGGED recovery model).

If you know that a transaction log file contains unused space that you will not be needing, you can reclaim the excess space by reducing the size of the transaction log. This process is known as shrinking the log file. Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can deallocated and returned to the file system.

Solution:

If database log files are full and taking up too much space…

  1. Perform a database log backup. (Not database backup).
  2. Shrink log file.

Shrink Log File:

Right click database name -> Task -> Shrink – Files

Make sure the File Type is “Log” and select “Release unused space”. The log file should shrink right away as indicated in the “Available free space”.

Note:

  • Make sure the log backup is not running or it will not actually shrink the log file.
  • Make sure there are enough space for both the log backup and log files.
  • Make sure to setup a purge process to remove .bak files from drive after a certain period of time.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file?view=sql-server-ver15

Last modified: November 26, 2019

Author

Comments

Write a Reply or Comment