✎ [V7] FAQ: SQL transaction log file size is not reduced / shrunken after a Transaction Log backup (in ODBC backup mode)? [Ahsay Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
===== [V7]  FAQ: SQL transaction log file size is not reduced / shrunken after a Transaction Log backup (in ODBC backup mode)? =====

<html>
 <b>
  Article ID:
 </b>
 5353
 <br/>
 <b>
  Reviewed:
 </b>
 2018-09-11
 <br/>
 <br/>
 <b>
  Product Version:
 </b>
 <br/>
Ahsay Software: 7.11.0.0 to 7.x 
<br>
OS: All platforms 
</html>

<html>
<FONT COLOR=red SIZE=+1>
<B>ATTENTION</B>
1st January, 2022: v7 officially End-of-Life [<A HREF=https://wiki.ahsay.com/doku.php?id=public:announcement:ahsay_v7_eol>details</A>]
</FONT>
</html>
===== Description =====
<html>
SQL transaction log (.ldf) file size is not reduced / shrunken after a Transaction Log backup (in ODBC backup mode)?
<br>
<br>
Note:<br>
This article only applies to MS SQL Server backup set with ODBC backup mode.
</html>

===== Contents =====
<html>
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.
<br>
<br>
<img style="width:70%" src="http://wiki.ahsay.com/lib/exe/fetch.php?media=public:sql_transaction_log_backup.PNG"/>
<br>
<br>
A transaction log file (.ldf) of a database records all transactions and the database modifications made by each transaction. &nbsp;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 <u>within</u> the transaction log file are cleared to be re-used). 
<br>
<br>This <u>does not</u> reduce / shrink the physical file size of the allocated log file on the operating system.  
<br>
<br>
Microsoft's defination of transaction log truncation (<a href="https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-2017">https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-2017</a>)
<br>

<blockquote>
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. 
<br>
<br>
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.
<br>
...
<br><br>
Note:<br>
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.
</blockquote>


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.
<br>
<br>
<img style="width:60%" src="http://wiki.ahsay.com/lib/exe/fetch.php?media=public:sql_log_shrink.png"/>
<br>
<br>
According to Microsoft (<a href="https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file?view=sql-server-2017">https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file?view=sql-server-2017</a>):
<br>
<br>
<blockquote>
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.
</blockquote>


The Shrink File operation must be performed <u>manually</u> (no backup type will trigger a shrink operation) to reduce the physical size of a log file.
<br>
<br>


Now, with the above information in mind, to maintain the size of the transaction log file from getting out of control, perform regular Transaction Log backup (in ODBC mode). 
<br>
<br>
Note:<br>
More frequent log backups have the added advantage of increasing the frequency of log truncation, which helps maintain the log file at a manageable / smaller size.
<br>
<br>
To reduce the size of the log file if it had already grown out of control:
<br>
<br>
<ol>
<li><font color=black>Perform a Full database backup (in ODBC mode).
<br>
<br>
<img style="width:70%" src="http://wiki.ahsay.com/lib/exe/fetch.php?media=public:sql_full_backup_b.PNG"/></font></li>
<br>

<li><font color=black>Perform a Transaction Log backup (in ODBC mode).
<br>
<br>
<img style="width:70%" src="http://wiki.ahsay.com/lib/exe/fetch.php?media=public:sql_transaction_log_backup.PNG"/>
</font></li>
<br>

<li><font color=black>Reduce the transaction log file size physically by using the Shrink File option.  For instruction, refer to the following document from Microsoft:
<br>
<br>
<a href="https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file?view=sql-server-2017">https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file?view=sql-server-2017</a>
<br>
<br>
Note:<br>
You should only perform a Shrink File operation on the transaction log file as last resort (e.g. running out of disk space).</font></li>
</ol>
</html>



===== Keywords =====
sql, log, transaction, ldf, mdf, small, smaller, reduce, shrank, shrunk, shrunken, shrinking
User Tools

Site Tools

Page Tools
