Database log files may continue to grow even though transaction logs are backed up
Published 23 March 2017
In ordinary circumstances, periodic log backups of databases that use the FULL recovery model will prevent the database log file from growing. When a log backup is performed, the backed-up transactions are marked as free in the database log and the space can be reclaimed by new transactions.
However, when the number of transactions being recorded in the log exceeds the amount of reclaimable space freed by a log backup, the database log file will grow. If this happens, you should shrink the log file manually. For more information, see this Microsoft support article.
Shrinking the log file manually will shrink the log file to a size that will hold all of the current transactions (plus a few percent more), but this must be done using a SQL query or management utility such as Management Studio. SQL Backup won't do this for you.
To shrink a log file, you can:
- use DBCC SHRINKFILE
- in SSMS, right-click the database and select Tasks > Shrink > Files
For more information, see the SQL Server documentation on shrinking files.