asked on

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

chapmandew

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

venk_r

ASKER

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?

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

venk_r

ASKER

thanks for the reply

venk_r

ASKER

thanks

chapmandew

You're welcome, but this was not a B answer.
Tim