September 26, 2012 at 12:05 pm
Hi,
I am curious about how to determine how big a log file should be? Is there a standard forumla I can follow if I am not looking into things too deeply? Lets day I have a 10gb db... how large should the log file be?
Also, I dont have the numbers, but at work our manager requested 200gb of space, and while I thought that was going to end up just adding 200gb to the growth of the db, the DBA came back with all sorts of crazy numbers on the additional space needed on each drive to do that (due to where the filegroups were, etc), and surprisingly, none of the numbers came close to 200gb. I asked him about it and he gave a vague reason for it and I can't remember all of it. I guess it was understood that the 200gb was for the increase altogether, so that would have to include the db files AND logs.
If anyone has any insight on how this was done please let me know.
September 26, 2012 at 12:16 pm
KTG (9/26/2012)
Hi,I am curious about how to determine how big a log file should be? Is there a standard forumla I can follow if I am not looking into things too deeply? Lets day I have a 10gb db... how large should the log file be?
Also, I dont have the numbers, but at work our manager requested 200gb of space, and while I thought that was going to end up just adding 200gb to the growth of the db, the DBA came back with all sorts of crazy numbers on the additional space needed on each drive to do that (due to where the filegroups were, etc), and surprisingly, none of the numbers came close to 200gb. I asked him about it and he gave a vague reason for it and I can't remember all of it. I guess it was understood that the 200gb was for the increase altogether, so that would have to include the db files AND logs.
If anyone has any insight on how this was done please let me know.
There is no magic number of how large a log file should be.
To get the insight on how your coworker calculated the space requirements you should ask them. There a number of things that could go into that but only the person who did the calculation can answer it. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2012 at 12:21 pm
No formula. To get an estimate of log size, set up a DB on a test server, run a simulated load on it (including maintenace) while doing the normal backups, see what the log grows to, add some for safety and that's a good a start number as you'll get.
At minimum, the log must be large enough to accommodate the largest single transaction (usually an index rebuild) or the largest volume of transactions between normal log backups, whichever is larger
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2012 at 12:28 pm
Hi I wish I could ask that DBA but he has since moved on, that's why I am coming to you guys 🙂
Can I see how much space a table/index is using via an SQL Command? Lets say I have TableX and ClusteredIndexA and NonClusteredIndexB.
September 26, 2012 at 12:30 pm
Or better yet, is there a query or command I can run to see how much space each object is taking up in the DB? That approach might be better, but I would love to know for both cases.
September 26, 2012 at 2:23 pm
The amount of space used by the database isn't determinative of the log size. It's the transactions that come through. You can have a gigantic database, but a very small log. You would need to measure the transaction throughput, determine how much data is being manipulated, then that number can be your log size estimate. But it's still just going to be an estimate. For example, how big are your indexes? Rebuilding those is sometimes more costly than a day's worth of transactions.
Also, we're not mentioning, what is your recovery model? Do you need to do point in time recovery? This means you have to do log backups and deal with maintaining those files.
There's lots to take into account here.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 26, 2012 at 2:50 pm
KTG (9/26/2012)
Or better yet, is there a query or command I can run to see how much space each object is taking up in the DB? That approach might be better, but I would love to know for both cases.
Here is how I understand it:
Read operations are not recorded in the transaction log so I can have a reporting database thats got 500gb worth of data and indexes but a small log file because nothing changes.
On the other hand I could have a 5gb database with a bigger log file because its an OLTP.
Knowing index or table sizes won't help I don't think.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply