August 15, 2011 at 9:04 am
my sql server backups used take 10 minutes to an average of 45 min complete. lastnight, I ran a SQL backup before the end of day processing and it took 1hr 27min though there were no users present on server ?
Can any one tell me, what could be the possible reason for that and also please address me following questions?
1. what are issues with SQL 2000 when moving from “Full” to “Simple” recovery mode?
a. Could moving from Full to Simple cause these performance problems?
2. What should we look for in SQL Profiler?
August 15, 2011 at 9:26 am
10 minutes to an average of 45 min. That itself is quiet a variation. backups usually dont wont have that much variation in time taken if taken regularly. It might vary by 10 min and not 30 minutes.
What is the destination of your back up? Local folder or network folder? With SQL profiler you wont see much for this. You might want to look at perf mon. Watch for IO bottle necks on your DB server and your back up destination folder.
-Roy
August 15, 2011 at 11:44 am
Thank you very much Roy.
i am taking on network path only.
is there any thing we can do to fix this problem?
i would be glad to you, if you suggest me.
Regards,
Harish.
August 15, 2011 at 12:18 pm
Have you had a radical increase in the size of the database? If it's exactly the same size (or near enough to it), then I'd guess some form of contention. Are there other processes running at the same time that could cause the backup to slow down?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 15, 2011 at 12:49 pm
smileswithharish86 (8/15/2011)
1. what are issues with SQL 2000 when moving from “Full” to “Simple” recovery mode?
Switching the recovery model will break up your log backups chain. So when you switch recovery model from full to simple then tyou should have a full backup immediately other wise the point in time recovery is not possible in case of failure.
Could moving from Full to Simple cause these performance problems?
database backup time usally depends on the size of the database and also see any concurrency of other jobs with the backup job. The good thing is to verify the sizes of old backups compared to the one's delayed.
2. What should we look for in SQL Profiler?
Backup is an High IO operation, So as Roy said you should check on the stats of IO performance in perfmon during your backups are made.
August 15, 2011 at 1:01 pm
What you could do is to take the back up to the local drive and then copy it over to your network share.
The problem with network back up is that, the speed of the back up will depend on the Network congestion, Disk IO of DB Server and the Disk IO of the Network server. You can reduce it by doing the back up locally.
If your DB is not growing at an alarming rate, the back up should take almost the same time. Are you doing compressed back up?
-Roy
August 15, 2011 at 1:57 pm
Thanks all
i will check perfmon for I/o stats.
as Roy said i will take backup locally and move to network later.
Thank you so much for your timely help ROY :-).
once i am done ,will update you guys.
Regards,
Harish
August 16, 2011 at 2:19 am
Switching the recovery model will break up your log backups chain. So when you switch recovery model from full to simple then tyou should have a full backup immediately other wise the point in time recovery is not possible in case of failure.
A differential backup can also do that!!!
August 16, 2011 at 1:06 pm
As Grant alluded to, did the size of the backup itself increase? That would be the first indicator of a root cause to me. If that's not the case, like everyone else mentions, the cause is likely an I/O increase and contention, in which case moving the backup to another timeframe should cure the ills as well....
-Patrick
Patrick Purviance, MCDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply