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 |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2003-02-12 : 14:03:30
|
| Our production system is currently doing log backups every 15 minutes; the idea being that we can live with a data loss of up to 15 minutes. However, when the backups happen, DB performance slows down significantly for the 30-45 seconds the backups take. Occasionally, they log backup seems to cause a lock cascade, and a bunch of users see really slow performance for a bit.We can't reduce the frequency of log backups. I'm thinking about increasing it, though, figuring that if we did it every 5 minutes, there'd be roughly 1/3 as much data to write, so we'd see more frequent performance hits of lesser severity.However, something is telling me that there's a point of diminishing returns; that even if we did them at 1 minute intervals, some amount of overhead would cause the total performance hit to skyrocket.Does anyone have any advice or experience to share?Thanks-b |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-02-12 : 15:02:23
|
| From sql-server-performance.com:"If your transaction log backups are affecting your users, consider backing up the logs more often so they are smaller. The smaller they are, then the less impact there will be on the server when the backup occurs. It is not uncommon to perform transaction log backups as often as 5 to 15 minutes, depending on transaction activity and on how much data you are willing to use, assuming the database becomes corrupt." |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-02-12 : 15:36:01
|
| Thanks for the info, tduggan!Next question: If I move to 5 minute intervals, should I be concerned that my nightly DB backup takes 20 minutes, and there would be 3 or 4 transaction log backups scheduled during that time? I see that I could use the "daily frequency" to create a window when transaction log backups don't happen, but quick research on google seems to indicate that the "starting at" and "ending at" times can't span days (that is, with the DB backup happening from 02:00:00 until 02:19:00, I can't set a starting time of 02:25:00 and an ending time of 01:55:00). I guess I could do that with two seperate transaction log backup jobs, though.Should I worry about that, or will SQL server be smart enough to just skip transaction log backups that happen during the full backup?Thanks!-b |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-02-12 : 15:45:10
|
| Don't be concerned about full backups and transaction log backups occurring at the same time. We do transaction log backups every 15 minutes (at the top of the hour, 15 after, 30 after, and 45 after), and also do full backups once a day (some happen at the top of the hour and others 30 after). So, a couple of times per day, we have databases being backed up at the same time that its transaction log backups are occurring. Keep in mind that a full backup is a snapshot of the database at the time that the full backup kicked off. |
|
|
|
|
|
|
|
|
How often to do log backups?