[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Determining The Transaction Log Size

Posted on 2010-04-09
7
Medium Priority
?
167 Views
Last Modified: 2012-05-09
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
0
Comment
Question by:venk_r
  • 3
  • 3
6 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 30213872
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
 
LVL 8

Author Comment

by:venk_r
ID: 30216005
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
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 30217046
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 8

Author Comment

by:venk_r
ID: 30223158
thanks for the reply
0
 
LVL 8

Author Closing Comment

by:venk_r
ID: 31712763
thanks
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 30223397
You're welcome, but this was not a B answer.  
Tim
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question