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 |
|
Ned
Starting Member
16 Posts |
 Posted - 2004-06-11 : 11:28:42
|
| Hi,Recently I noticed our transaction log on our databases were very large. I made a backup, detached the database, deleted the transaction datafile and then reattached the db. This process recreated a new log file. I am a little confused on why it got so large. We currently backup our database at 11pm. I back up the transaction log at 11am. This way if the database crashes at 2pm, I can restore the database backup and then the transaction backup and I am reset to the 11am settings (Please tell me if this is incorrect. I wrote this out just to make sure I am understanding the trans log use)If the trans log is only helpful from the last database backup, then why does the trans data file keep on growing? Wouldn't it make sense that after each database backup the trans log would be reset? This isn't happening, so obviously I am missing something.Our database isn't mission critical so having a daily backup and one transaction log backup seems sufficient. I have read some articles on the theory behind the trans log but I haven't seen much real life examples (i.e. how to restore the trans log, good scheduling practices, why it keeps on growing etc)I realize I am kinda rambling but and suggestions would be appreciated.Thanks |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-11 : 12:09:48
|
| You might want to set your recovery mode to simple and just do full with a diff in the middle of the day. This should be more efficient if you're only doing one transaction log backup a day.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-11 : 12:26:54
|
| Or dump the tranny log more frequently....what's very large?Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-11 : 12:55:26
|
| Well the transaction log is "reset" after the BACKUP LOG is done. But once a day dumping the log isn't very frequent. It is growing and growing because you aren't backing it up often enough since you have a lot of data going through your system. We backup ours every 15 minutes. I would suggest at least every 2 hours. How to restore the log:RESTORE LOG...In your situation, if you weren't able to backup the final log, you would lose everything after 11am. So your current implementation is only able to restore up to 11am. We can't afford to lose more than 15 minutes of data due to the high volume of data flowing through system and the criticalness of it. If yours isn't critical or you can afford to lose all of the data since the last full backup, then change the recovery model to simple as Derrick mentioned.Tara |
|
|
|
|
|
|
|
|
Transaction Logs