Determining The Transaction Log Size

How to determine the Transaction Log file size.
Currently the OLTP database (SQL SERVER 2008) takes avg 5 million transaction each day and we have kept in simple recovery mode.We have like 3 month data retention  and the size of rthe db  is around 150G.In next couple of months we will be changing to full recovery mode with 12 month data retention (around 1 terabyte) and also implement transactional  Replication.
The log drive  currently sits on a 250G fibre channel drive.
How much would I need increase the size to fit in the above requirements?
Thanks
LVL 8
venk_rAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
Great question...but it is kind of guess work.  The reason I say that is because it depends on a few different things:
1.  how often are you going to be doing trans log backups?
2.  are you planning on having any long running transactions

The more often you do log backups, the more often the log is truncated (reused).

One important thing to note is that you want to keep the number of Virtual log files to a minimum..they'll be created when the log file is expanded...so, set the log file large (maybe 20gb or so to start) and have it grow in large increments.  Doing so will aid in recovery should you ever need to do so.

Here is a list of things that prevent log truncation:  http://msdn.microsoft.com/en-us/library/ms345414.aspx
0
venk_rAuthor Commented:
Thanks for the reply.
We are planning to do the log backups may be evry 2-3 hrs.
And no , not much long running transactions.
Remember we are configuring transaction Replication.

Being said would it ok if I place it in a drive with say 400G in size?
0
chapmandewCommented:
I would imagine that would be fine, yes.  If it starts getting out of hand then I would start doing more frequent log backups.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

venk_rAuthor Commented:
thanks for the reply
0
venk_rAuthor Commented:
thanks
0
chapmandewCommented:
You're welcome, but this was not a B answer.  
Tim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.