October 15, 2003 at 6:34 am
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
October 15, 2003 at 10:43 am
Strange. Haven't seen anything like this, but suspect you may need to call PSS on this one.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
October 15, 2003 at 11:45 am
The increase of backup time during the peak period could be caused by large amount transactions that need to be backed up. The backup job could also be delayed for execution due to some kind of blocking before the backup can be kicked off. But I don't see the slowness users complained are related directly to the backup itself. Monitor overall system performance from hardware to SQL Server to see whether you could ping out what happened exactly for the slow performance.
October 16, 2003 at 3:41 am
The main problem about increasing transaction log backup frequency to cope with peak loads is that you are imposing a high frequency of log backups when load is low.
We backup our trans log every hour, and use an alert to cope with peaks within that timeframe. We have an alert configured for each database that triggers when log used % passes a threshold. The alert has an auto response of running a trans log backup.
For certain databases, we experience very high peaks in usage, maybe 2 or 3 times per week, at different times in the day. For these databases, the log alert threshold is set to 5% or 10% of the log size. The response for these just runs the trans log backup, and does not inform the DBA as we regard this situation as BAU.
For other databases we never see any significant peak. For these the alert threshold is normally 85% and in these cases the alert will also e-mail the DBA.
The end result is that databases with high peak loadings get their trans logs automatically backed-up while they are still small, resulting in fast log backup times, without imposing a high trans log backup frequency during quiet times.
Ed Vassie
All information provided is a personal opinion that may not match reality.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 16, 2003 at 5:04 am
The alert is a nice technique. I went for something simpler (uglier), I check the file sizes and even they are beyond a threshhold, I backup. Works for me because I have a lot of log files that should all remain about the same size.
Are you backing up to a device or to separate files? If you're using a device, I wonder if there is overhead associated with SQL figuring out where to write to? Wouldnt think so, but maybe worth testing.
I had problems a couple years ago doing the log backups, just didnt have enough IO to support it, had to schedule for when they employees were on break.
Andy
October 16, 2003 at 8:00 am
I would have you monitor the disk I/O to
look for a bottleneck. You may not have
enough fiber connections on the SAN.
What drives are on the SAN?
For this example,(tran logs and backup devices each on seperate RAID 1) is this the same LUN? How is the SAN cache used?
How many LUNs is SQL using?
Where are the system db files located?
Where are the db files located?
Where is the destination of the SQL backup?
What is the memory on the server,
how much is SQL using?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply