March 26, 2014 at 1:26 pm
WebTechie38 (3/26/2014)
This is an incredible day!!I want to thank all of you for your responses. I am in constant learn mode with SQL Server. From asking one question, I have learnt so much.
You guys explained so much...Wow. Thank you.
1) I now understand more of the transaction logs and their value regardless of the recovery model.
2) This particular database is a highly transactional database. The vendor has said that it needs to have a simple recovery model.
3) Since, it was in simple, I didn't understand why I was getting errors on the transaction log.
4) The Database file is 300 GB with with autogrowth of 100 MB.
5) The log file is 9 GB with autogrowth of 10 MB.
Based on your answers, I have a much better understanding of what is going on.
Thanks again for sharing your knowledge.
Tony
4. Even this autogrowth amount seems low. It sounds like the database is fairly active, I would think about changing this value to at least 2000, if not more. Frequent database autogrowths are also not a good thing.
5. This is very very low, I would set it at 1000 MB
March 26, 2014 at 1:41 pm
WebTechie38 (3/26/2014)
This is an incredible day!!I want to thank all of you for your responses. I am in constant learn mode with SQL Server. From asking one question, I have learnt so much.
You guys explained so much...Wow. Thank you.
1) I now understand more of the transaction logs and their value regardless of the recovery model.
2) This particular database is a highly transactional database. The vendor has said that it needs to have a simple recovery model.
3) Since, it was in simple, I didn't understand why I was getting errors on the transaction log.
4) The Database file is 300 GB with with autogrowth of 100 MB.
5) The log file is 9 GB with autogrowth of 10 MB.
Based on your answers, I have a much better understanding of what is going on.
Thanks again for sharing your knowledge.
Tony
Hi -
It is our pleasure to help. However, with that said, I'd like to address some of your points...
You describe the database as highly transactional. As such, I believe that your autogrowth for both PRIMARY and TLOG is too low. For a 300GB I would set autogrowth at 1000MB for both PRIMARY and TLOG, and would set TLOG at 30GB out of the gate. Remember that auto-growth events can be expensive to running queries. Reducing how often they happen is an excellent idea.
Whether you use full or simple recovery mode does not depend on the vendor. It depends on you as the customer and how much recovery you need. In the event of a catastrophic server failure, or a major program bug causing bad data in the database, how much can you afford to lose? Can your company afford to lose everything since the last backup? If the answer is YES, then you are fine. Any other answer should drive you to consider full recovery mode and transaction log backups every 5-60 minutes. This would give you point-in-time restore plus drastically reduce your exposure to data loss. That is the sole purpose of full recovery mode.
SQL 2008 is well designed to handle TLOG backups every 15 minutes. I do that on our production ~2TB database. Caveats would be not to back up onto the same SAN or drive array where the PRIMARY and or TLOG files reside.
Also with a 300GB database, you might wish to consider multiple files in PRIMARY and perhaps a second file group for nonclustered indexes, in order to give SQL Server more spindles to use. But that's sort of beyond the scope of this issue.
Thanks
John.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply