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…
- Perform a database log backup. (Not database backup).
- 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.
- In the event that there is no disk space left to perform a backup, switch database backup mode from Full to Simple.
- Make sure to setup a purge process to remove .bak files from drive after a certain period of time.
- If the log file still does not shrink, try performing a full database backup and a transaction log backup and then try shrinking the file again.
References:
Comments