Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
boo2
Starting Member
5 Posts |
Posted - 2008-11-12 : 10:33:16
|
I found how to 'resolve' the issue here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56434But this doesn't tell me what I'm doing wrong as far as the design of this thing.One, should I be using Simple or Full backups? What are the pros/cons of each - I don't care about the data so I guess Simple (was Full by default I guess).When people say 'uncommitted transactions' that could be causing the log file to grow, what do they mean? Wouldn't *that many* uncommitted transanctions just time out and auto-rollback at some point?Does the log growing so large possibly indicate a problem with a stored procedure somewhere or bad design? What should I be looking for that is causing this? If it's natural for log files to just grow forever - why do you have to manually shrink and truncate? Why wouldn't this be automatic.I'm going to be honest...I'm just an application developer who writes SELECT/UPDATE/INSERT/DELETE statements - I'm not going to pretend to be a DBA, so this is where I need help from you experts.Thanks much! |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-12 : 12:31:14
|
quote: Originally posted by boo2 I found how to 'resolve' the issue here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56434But this doesn't tell me what I'm doing wrong as far as the design of this thing.One, should I be using Simple or Full backups? What are the pros/cons of each - I don't care about the data so I guess Simple (was Full by default I guess).Simple recovery model doesn't let you do transaction log backup so you won't be able to recover point in time When people say 'uncommitted transactions' that could be causing the log file to grow, what do they mean?means it hasn't been committed.Wouldn't *that many* uncommitted transanctions just time out and auto-rollback at some point?Yes, If it is canceled in middle of operation to maintain transaction ACID propertiesDoes the log growing so large possibly indicate a problem with a stored procedure somewhere or bad design? What should I be looking for that is causing this? You should be looking if there is frequent transaction log backup in place if Full Recovery model. If it's natural for log files to just grow forever - why do you have to manually shrink and truncate? Why wouldn't this be automatic.It usually grows huge when you have Bulk insert,massive deletes and Rebuild index because it is logged.so you have to frequently take transaction log backup like every 15-30 mins to control . If you are in simple recovery model, it will be truncated at checkpoint.I'm going to be honest...I'm just an application developer who writes SELECT/UPDATE/INSERT/DELETE statements - I'm not going to pretend to be a DBA, so this is where I need help from you experts.Thanks much!
|
|
|
boo2
Starting Member
5 Posts |
Posted - 2008-11-12 : 17:04:52
|
quote: Wouldn't *that many* uncommitted transanctions just time out and auto-rollback at some point?Yes, If it is canceled in middle of operation to maintain transaction ACID properties[quote]So when it rolls back, there's no reason for the log to be growing then right?[quote]It usually grows huge when you have Bulk insert,massive deletes and Rebuild index because it is logged.so you have to frequently take transaction log backup like every 15-30 mins to control . If you are in simple recovery model, it will be truncated at checkpoint.
This database I'm sure has no bulk insert or deletes. How do I get the log to back up to run on a schedule, or at all for that matter - again, just a dumb programmer...not a DBA.If I use simple recovery model will this make all these problems just 'go away'? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-12 : 17:24:14
|
Yes log growth will be minimal if you use simple recovery model but you lose opportunity to recover point in time. Determine how critical your Database is and change your recovery model. Remember if you are in Full Recovery model, to minimize tran log growth you need to take frequent log backup. |
|
|
boo2
Starting Member
5 Posts |
Posted - 2008-11-12 : 18:29:55
|
quote: Remember if you are in Full Recovery model, to minimize tran log growth you need to take frequent log backup.
How do I configure this? Or can I only do it manually? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-12 : 22:31:16
|
You can either maintenance plan wizard or put the script for Full backup and transaction log backup in job in SQL Server agent.Specify schedule for full backup and log backup . Please read about Recovery model and backups in Booksonline. |
|
|
|
|
|
|
|