Ahsay Software: 220.127.116.11 or above
OS: All platforms
SQL transaction log (.ldf) file size is not reduced / shrunken after a Transaction Log backup (in ODBC backup mode)?
This article only applies to MS SQL Server backup set with ODBC backup mode.
To understand why is the physical size of a transaction log (.ldf) file not reduced / shrunken after a Transaction Log backup (e.g. log truncation), you must first understand the difference between transaction log truncation and shrinking a SQL transaction log file.
A transaction log file (.ldf) of a database records all transactions and the database modifications made by each transaction. When a Transaction Log backup (in ODBC backup mode) is performed, which triggers a log truncation operation, inactive portion of the the transaction log file are marked as re-usable (e.g. logical space within the transaction log file are cleared to be re-used).
This does not reduce / shrink the physical file size of the allocated log file on the operating system.
Microsoft's defination of transaction log truncation (https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-2017)
Log truncation frees space in the log file for reuse by the transaction log. You must regularly truncate your transaction log to keep it from filling the alotted space.Shrinking a SQL transaction log file on the other hand (e.g. Shrink File operation), is used to reduce physical size of a physical log file.
Log truncation deletes inactive virtual log files (VLFs) from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the physical transaction log. If a transaction log is never truncated, it will eventually fill all the disk space allocated to physical log files.
Log truncation does not reduce the size of the physical log file. To reduce the physical size of a physical log file, you must shrink 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.The Shrink File operation must be performed manually (no backup type will trigger a shrink operation) to reduce the physical size of a log file.
sql, log, transaction, ldf, mdf, small, smaller, reduce, shrank, shrunk, shrunken, shrinking