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
@LauraV.
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,
USE database_name
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
USE databasename
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.
Imran.
Reference : Pinal Dave (https://blog.sqlauthority.com)
9 Comments. Leave new
nice to read above artical thanks to both Imran as well as Pinal who appreciated his knowledge and placed on his site.
thanks a lot and hope for more like this in future.
Hi,
As per LauraV mentioned the problem,i just want to suggest you can create the alert in sqlserver agent with 90% thresold value which executes the transaction log backup job.So if in the middle of scheduled transaction log backup job if the log gets full then alerts will automatically execute it.
Kindly reply suggesteion.
– Prakash
hello
i see your artical it greate but i have some issues i am using ms sql 2005
i am basically a web developer but developing a project using ajax asp.net and ms sql 2005
well i have some issue on store procedure write know need one
well i have two tables abc and xyz
i am using crusor get first row of table abc and then password to xyz to get specfic data from xyz and then gaing untial first table end and then insert into ta temporary table
there is isssue is when i insert into table when a abc data was not found then it also send a a value of abc in tem table
how can i avoid when xyz is empty then dont insert abc row in temp table
plz wait i need your help waiting
Hello Pinal,
Really a great article…thanks a lot…..
can u give me a link from where i get more information about the same….i.e. acitve n inactive transaction..
And i have one question to ask…
Is it possle to shrink log file during log-shipping..?
If yes then how..
Plzzzzzzz reply….
this site is very good :)
Hello Pinal, great article, however one aspect of @LauraV ‘s original question was the apparent continued growth of the log file. That comes down to two reasons: either her log file is not sized correctly for the transactional load of the supported application or she needs to increase the frequency of log backups on the database.
– Tim Ford
aka “@SQLAgentMan”
thanks for all.
I have been trying to work on a straight forward way to shrink the transaction logs on several databases (after backups have completed) and have a question.
Is there a script I can use to reorganize and shrink a transaction log in SQL 2005 after a backup?
Just backing the log up and doing a DBCC Shrinkfile doesn’t work, based on my research (please correct me if I am wrong), I am thinking it is because there is active data that was previously written near the end of the log. I also do not want to do a “with truncate only” and loose data.
In Management Studio, if I manually shrink the file and tell it to reorganize, it works, but I would prefer a script to do this.
Thanks
Is it really necessary to reduce the size of T-log file? Correct me, if I am wrong, but to complete the active transactions, the t-log requires certain amount of free space in the T-log. If the T-log is growing to a certain size, it really means such amount of space is required in the t-log to complete all the transactions.
If we shrink the log file, then SQL server has to auto grow the log file size whenever required, which will consume server resources with an impact on the overall performance.
Therefore, it is advisable not to shrink the database-log file, until and unless you are running out of disk space.