February 8, 2010 at 5:40 am
Guys I have a question in regards to database transaction log size. Ok let's say I create a 10 gig database my question is how big should the initial transaction log be? I know the log growth rate is set at 10 percent Is there some type of formula that allows you to calculate the log size for new databases for example a 20 gig database gets a 2 gig log. I know that log size is dependent on the database activity however I'm just curious in knowing of there's a base line. How do you all handle this do you just leave the defaults watch the database over time than adjust accordingly.
February 8, 2010 at 10:45 am
Right or wrong, I've always started out at 1/3 my datafile size..
so a 100mb database would have a 33mb log file.. of course, this changes fairly quickly as I see what my usage actually is
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
February 8, 2010 at 10:59 am
I don't have/do any calculations initially. I would set the log file size as a default size and growth at 10 MB. But as a best practice, I usually analyze the data growth and the log size growth initially for any database in my environment and then set at a final value considering its growth.
February 8, 2010 at 11:05 am
I usually start with the log file size set to 1/4 of the Data file size and most important, I would never have the autogrowth set in %, I would rather have it in MB size..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 8, 2010 at 11:07 am
Bru Medishetty (2/8/2010)
I usually start with the log file size set to 1/4 of the Data file size and most important, I would never have the autogrowth set in %, I would rather have it in MB size..
I agree.. I hate % increases
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
February 8, 2010 at 11:23 am
There are complications when setting the log file size low and then the growth to also be low. If you have a bunch of log growths, your log may become fragmented and consequently negatively impact performance.
I typically set the log a little larger - but this should all be calculated on expected transactions, and frequency of transaction log backups. Having your log file grow is not necessarily a good thing - but may be necessary from time to time. If you right-size your log so that it doesn't need to grow, then you will experience better performance (typically).
I prefer to set my log growth to MB as well - should an unexpected growth occur.
Kimberly Tripp discusses VLF's here:
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 8, 2010 at 2:06 pm
When in doubt, I review the following excellent articles:
http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 8, 2010 at 2:20 pm
I'm with Bru and others. Set it to grow by a decent percentage (use MB) of the original size.
So for a 10GB db, I'd think about whether I expect it to busy or not. If not, I might to a 2GB log and set growth at 500MB. If it's busy, I might be closer to a 3-4GB start and 1GB growth.
The key thing is to monitor the size of the log backups on a regular basis. Know how many bytes you generate between each log backup and size accordingly (with pad)
February 8, 2010 at 3:39 pm
Jeffrey Williams-493691 (2/8/2010)
When in doubt, I review the following excellent articles:http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
I must agree 😎
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply