Imran Mohammed continues to help community of SQL Server with his very enthusiastic writing and deep understanding of SQL Server architecture. Let us read what Imran has to say about how Transaction Log works and Shrinking of Log works.
Question from lauraV
Please help me understand. I am taking a full backup once a day, and transaction logs once every hour. Why is my LDF file not retaining a “normal” size? It continues to grow. I do not want to break the chain and use truncate only, though I have done this and it fixes the problem. I would very much like to understand the underlying problem.
thank you in advance
I suggest to all my readers to read the answer from Imran, this really explains what really goes on behind the scene of Transaction Log. I have highlighted some of the important keylines and keywords in his answer to lauraV.
Answer from Imran Mohammed
You did not mention which version of SQL Server you are using, Either 2000 or 2005, ( would be easy for us to narrow our answer to questions)
When ever you take transactional log backup, no matter if you scheduled it or if you take it manually, SQL Server by default will empty transactional log. Meaning after the transactional backup ( Be Careful NOT FULL BACKUP) SQL Server will remove inactive transactions from logfile.
Which means your transaction file is empty after you take transactional log backup, Empty doesnot mean your logfile became small, NO. the size of the log file will still be the same but it will be empty, all you have to do is shrink logfile, run below command,
DBCC shrinkfile (logfilename, 1)
and it will shrink the log file to its minimum size possible,
In SQL Server 2000:
Right click database name -> all task -> backup -> in the dialog box, select transactional backup, and click on options ( at the top left side of the box) you will see “remove inactive entries from Transactional log”.
This means when you take transactional Log backup, SQL Server is removing all inactive entries from SQL Server log file. This is default setting.
In SQL Server 2005:
Right Click database name->task ->Backup in the Dialog box, select backup type Transactional backup, and click options tab (at left side up) and under Transaction log section you will see “Truncate the Transaction log”.
Which mean when SQL Server 2005 performs transactional backup it truncates logfile. This is also default setting.
NO matter if you use SQL Server 2000 or SQL Server 2005, SQL Server by default (can be changed) will truncate log after performing Transactional backup.
So simple solution will be shrink log file after you take transactional log backup. You dont have to do it manually You can send this as a response to this transactional bacup job, when ever this job succeed, response to that would be run this script
DBCC shrinkfile ( logfile_name, 1)
That might work.
I will take one more minute to explain what is this active and inactive transaction in the logfile . Please correct me if I am wrong.
This is how SQL Server works,
Note from Pinal – Following 4 paragraph is the most interesting part of whole discussion.
When a transaction comes to SQL Server, it first comes to transactional log buffer, and then it is hardened to disk ( log file, .ldf ) and then it is written to data file ( .mdf). Then we say the transaction is committed or it is inactive, because the transaction performed all the actions that it should.
On the other hand, when a transaction comes to SQL Server and it is entered into log buffer and also in transactional log, but not yet entered in data file, its still in the process, then we will say this transaction as active transaction.
One important point to think is, all the inactive transactions in the log file ( transactions which completed their tasks and are entered in data file) are also present in data file, and SQL Server is smart enough to think, inactive transactions are already in data file and also, they have been backed up( by transactional log backup) hence it thinks, this is the time to get rid of this data and it removes all the inactive transactions.
But for Active transactions, which are either incomplete or could not complete because of disaster ( sudden power failure….) will be stored in transactional log and will be called acive transactions untill they are entered in data file.
This will be little bit confusing…
Read more about this in books online.
Hope this helps.
Reference : Pinal Dave (https://blog.sqlauthority.com)