May 25, 2009 at 12:15 am
Dear All,
Evan after truncating/shrinking LOG files many times, I still have a some doubts regarding it.
Suppose the number of transactions in DB is same everyday and it doesn't vary.And I take LOG backup everyday at same
time (I donot shrink it.). The LOG size is 1GB today and am taking BACKUP now. So will the LOG size be 1GB by
tomorrow this time..? Or it will be more than 1GB..?
What I understand is that when we BACKUP LOG, it releases the space for SQL Server to use it again.
Since the number of transactions are same everyday, SQL Server should use the freed space and LOG size should remain same (1GB.) and it shouldn't grow.
Please correct me if I am wrong.
Thanks in advance.
May 25, 2009 at 12:49 am
The size of the transaction log is affected by the number of transactions and also by the size of the transactions. For example if I have a table that has 1000000 records I can issue the statement delete MyTable or the statement truncate table MyTable. In both cases I'll have one transaction that affects the same table and records, but the truncate table statement is minimally logged, so there will be a big difference in the log usage. Also you should remember that maintenance jobs such as index rebuild or index reorganize also take a lot of log space and those jobs don't run on a daily basis.
When you backup the log, the log's size doesn't change. After the backup, the log file will have the same size as it had before the backup log operation, but it will be able to reuse the space that had log records before the backup log operation. This will reduce the chance that the file will be full and the need to grow the file for more free space, but this scenario (the need for a larger file) might still happen.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 25, 2009 at 1:44 am
Adi Cohn (5/25/2009)
The size of the transaction log is affected by the number of transactions and also by the size of the transactions.Also you should remember that maintenance jobs such as index rebuild or index reorganize also take a lot of log space and those jobs don't run on a daily basis.Adi
Thanks for your immeidate reply.
Wel, BTW wot if the number of transactions and also the size of the transactions as exatly same..? And maintenance jobs such as index rebuild or index reorganize did not happen on both the days..?
Suppose everything is exactly same on both the days...
Still LOG will grow..? If so Why..?
May 25, 2009 at 11:14 am
San (5/25/2009)
Thanks for your immeidate reply.Wel, BTW wot if the number of transactions and also the size of the transactions as exatly same..? And maintenance jobs such as index rebuild or index reorganize did not happen on both the days..?
Suppose everything is exactly same on both the days...
Still LOG will grow..? If so Why..?
No - if everything is exactly the same, the same number and size of transactions - the log file will not grow. That is, as long as you are backing up the transaction log at the same time and interval every day.
Remember, you should be taking frequent transaction log backups - at least every 4 hours and I would recommend a minimum of every hour for a production system.
You can review the article in my signature for more information on managing transaction logs.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 26, 2009 at 1:38 am
Thank you somuch.
I do take backup of LOG every 4 hours in my production server.
Was just cleaing my doubts.I will do some testing on my home PC and will see the LOG file growth.
Once again thanks.
May 26, 2009 at 2:18 am
I don't think that there should be a rule of thumb about the time interval of log backup. It depends on factors such as the importance of the data in the database, the consequents of loosing part of the data, etc'. I've worked in places that had log backup every few hours and in other places that had log backup every few minutes. Can't say that one place took the correct decision and the other one didn't.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply