Why Does the Transaction Log Keep Growing or Run Out of Space?
Is it possible to have SQL Server 2008 R2 reset the transaction log for certain databases when the database is backed up?
Auto backup job in SQL Server takes a backup of database XYZ and also of it’s transaction log. When this backup has completed SQL Server will reset the transaction log (log file size will be increased to starting point)
Quoting Paul Randal here:
In the FULL or BULK_LOGGED recovery models, the only thing that
clears the log is a log backup.
“Reseting” the transaction log is basically what happens when SQL Server backups a database either during full or log. Read here to get more detail: http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx
The backup basically takes all that used space in the transaction log and clears any inactive log space that it can, so SQL will be able to reuse it instead of growing the log file out. The more frequent you do log backups, the more it clears that inactive space. Most people will refer to this as log management, with regards to SQL Server.