Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 LDF Size after Transaction

Author  Topic 

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2010-02-21 : 19:00:36
We have organised the Shrinking of the Data files on server and it went on well. Thereafter, it has again grows to its previous size of the LDF (Which is before the Shrinking Size) just after a transaction, I know due to its allocation (in terms of its occupational Size) that required for the LDF will gain its size. But what is the purpose being served with the shrinking?

Also, I have the Maintenance Job running to do the following like REINDEX, RECOMPILE ALL SP’s; UPDATE STATISTICS and CHECKDB which takes around 2 ½ hours but other day it took for the SAME around 4 ½ hours, at the same time the LDF’s which were Shrunk has regained its ORIGINAL SPACE.

Can anyone please clarify?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-02-21 : 19:22:10
The scenario you describe is exactly why you shouldn't shrink the transaction log down. Stop shrinking it as you are causing a performance problem since the server needs that space for maintenance jobs and large transactions.

There is no purpose to shrinking down the data files unless you know you no longer need that space due to a big purge of data or some other change in the database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2010-02-21 : 22:18:32
Thanks for the reply.

However, Can you explain how to use the checkpoint so that All the transactions which have been changed since then will be writting back to MDF location, using CHECKPOINT business. So that LDF transactions were liberated and fresh transactions can be stored.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-21 : 23:50:50
It because you have Reindex jobs that run everyday. You Don't have to rebuild all indexes.Just check Tara's Script which smartly checks for fragmentations and decides to rebuild or reorganize.

You have to understand logic of Transactions Logs. Logs files have Virtual Log Files and MINLSN Decides for active and inactive portions.

When you perform Transaction Log Backup, all inactive VLFs are freed up and continuously doing log backup will make manage the log growth as older inactive VLFs can be used again.

When you are shrinking log files and it again grows up there are numerous unnecessary VLFs created and thus can hinder backup,DML and restore performances. Preset the initial size for Log files.
Go to Top of Page

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2010-02-22 : 18:28:03
You mean to say I need to Re-write the Log File Initial sizes?

on Doing this will the next transactions will pick it up the requisite size it needed.
Clarify.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 03:14:42
No that was not what was said.

Backup your TLog regularly (every 15 minutes is a good solution)

Do not Shrink it - unless you have done a one-off huge deletion, or something similar which made the LDF grow unusually large

" Can you explain how to use the checkpoint so that All the transactions which have been changed since then will be writting back to MDF location, using CHECKPOINT business. So that LDF transactions were liberated and fresh transactions can be stored"

You don't need to do that. Making a Backup of the TLog will set the space used by all committed transaction as being "available" for reuse by new transactions. Thus the file will be reused, rather than growing bigger.

(This assumes that you are using Full Recovery Model, rather than Simple Recovery Model)
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-23 : 13:52:58
What I mean is you need to resize you Log file to Avoid Large VLFs.
Go to Top of Page
   

- Advertisement -