SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 LDF Size after Transaction
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dbalearner
Constraint Violating Yak Guru

256 Posts

Posted - 02/21/2010 :  19:00:36  Show Profile  Reply with Quote
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

USA
35951 Posts

Posted - 02/21/2010 :  19:22:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

256 Posts

Posted - 02/21/2010 :  22:18:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/21/2010 :  23:50:50  Show Profile  Reply with Quote
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

256 Posts

Posted - 02/22/2010 :  18:28:03  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 02/23/2010 :  03:14:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/23/2010 :  13:52:58  Show Profile  Reply with Quote
What I mean is you need to resize you Log file to Avoid Large VLFs.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000