SQL Server Performance Forum – Threads Archive

Transaction Log backup times increasing

I recently changed my trans log backups from every hour to every 15 minutes, (tran logs and backup devices each on seperate RAID 1). I did this because once or twice a day, (usually during peak usage) the backup times would jump from a few seconds to a minute or more. When this happened users started complaing of slowness. I increased the backup interval to 15 minutes hoping to avoid this. What I’ve found instead is that the backup times for all logs are increasing steadily by a few seconds every time. So now all my backups are taking over 1 minute everytime they run. It has taken 3 days to reach this level but I don’t understand why. I init the tran log once a week right after the full backup but the backup logs themselves are not any larger than before I increased the backup interval. The other thing that perplexes me is that when I look at the job steps in detail it says: ‘BACKUP LOG successfully processed 65 pages in 0.683 seconds (0.777 MB/sec).’ This came from a backup that reportedly ran for over 1 minute. Runnig SQL 2000 Enterprise SP3a in a 2 node cluster connected to a fiber SAN. Any help, suggestion or comments would be greatly appreciated as this has me stumped. -rob

---

ANy network issues or parallel jobs are running during this slow-execution time?
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

No network issues that correspond. No other jobs running in parallel except for the 8 tran log backups that kick off at the same time.

---

Well this explains a bit of information, try to schedule the Tlog schedule for each database with a difference to 5 mins. Also as of now you may get more information by running PERFMON to capture counters for further assessment. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

Thought about that but worst case scenario I lose 40 minutes of data, correct? I have been running perfmon and watching disk counters as well as some other counters. Other than heavy read activiy on the log backup drive during the backup, everything else looks good. I’ve also considered:

• moving the log backup interval back to every 2 hours to see if it makes any differnce
  
• Creating additional backup devices for each log
• renaming, moving and initing the log backups once a day

Again, thanks for your advice. Much appreciated.

---

BTW what are the database sizes involved in this TLog backup? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

350 MB
10.2 GB
530 MB
2.4 GB
1.9 GB
500 MB
1.4 GB
8.4GB All backups are taking approx the same amount of time. I can break this down into MDF vs LDF size if you want.

---

Thanks thats fine, and as I said before as a test try scheduling Tlog backup with a difference of 3 or 5 mins. between each database or between those more than GB sized databases. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

Thanks, I’ll try that first.

---

]]>