September 4, 2011 at 12:25 pm
Hi,
There is a db in SQL Server 2005 initially the full backup job was taking 40 minutes to complete but since few days it is taking around 1 hr. No much data has been increased in database. I checked fragmentation level of indexes, sufficient free space is also available on backup disk. Everything is fine still I could not find any solutions for this job. Still it is taking around 1 hr.
Thanks in advance.
September 4, 2011 at 12:51 pm
Index fragmentation won't have anything to do with backup time. Backup is an IO-bound operation, extents read from the data file written directly to the backup file. Check IO throughput and contention on source and destination drive, also check the errorlog for any mention of backup buffers.
I'm sure you've checked, but the data volume hasn't grown by half again?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 4, 2011 at 1:04 pm
yes the data volume hasn't grown by half, and I dont have knowledge about performance monitor tool.
September 4, 2011 at 1:54 pm
You can reduce the backup time by specifying multiple files instead of single file. Or try with Blocksize=65536 if you are using single device
September 4, 2011 at 2:26 pm
kp_kumar (9/4/2011)
You can reduce the backup time by specifying multiple files instead of single file.
Only if those files are on separate drives (not separate partitions of one drive)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 5, 2011 at 12:08 am
First thing I'd look at is: what else is happening on the SQL Server at that time?
Look for
1. Maintenance tasks ie re-indexing.
2. other Backup operations causing disk contention.
3. Introducted jobs that use DBCC commands.
4. Sometimes the Domain Admin push little thing like anti-virus agents - without notifiy the DBA
5. Windows Schedualed tasks that are doing stuff like moving files around.
Best of luck !
CodeOn
😛
September 5, 2011 at 2:58 am
Only backup job is running at scheduled time.
September 5, 2011 at 7:55 am
beejug1983 (9/5/2011)
Only backup job is running at scheduled time.
There has to be something else going on. Has to. SQL Server doesn't just randomly decide to slow down. Are you backing up across the network? It could be increased network traffic. You say you don't know about performance monitor, that's how you're going to be able to tell what's happening on your system in terms of CPU, I/O, network, and disk. My book introduces the concepts, provides a set of counters and some general numbers to look for. That'll get you started.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 5, 2011 at 8:52 am
Are you taking this backup in local disk or in network share drive?
September 5, 2011 at 10:53 am
Its SAN drive.
September 5, 2011 at 11:29 am
Can you run the backup command from the Query window
and then note the time taken to complete the backup and let us know
BACKUP DATABASE <your dbname> TO disk='sandrive\your dbname.bak'
Also console the SAN drive admin to ensure the drive performance.
September 5, 2011 at 11:38 am
Ask what changed to the san / network admin.
Things just don't slow down on their own.
September 8, 2011 at 9:39 am
Hi Beejug;
I've recently had a drive get so fragmented it would cease to function altogether; it was being reported as having very little fragmentation only to find out, by running a better defrag, it was actually extremely fragmented.
It's because of that and experiences like yours that I now stick to the top-rated defrag. Prior to this I never suspected this was even possible so it was a long frustrating discovery for me.
There are some other good suggestions by the prior posters but you may want to completely rule out this possibility in your SQL also.
Here's a link to a FREE DEFRAG TRIAL[/url] for the product I used. If it's not the version you need you could contact them to see if there is a trial available for the version you might require. This is a rare problem as far as I know but, at the same time, if it is what is happening to your SQL it must be ruled out 100% because doing other things may make it worse.
Hope this helps
Cheers
September 8, 2011 at 9:54 am
GilaMonster (9/4/2011)
kp_kumar (9/4/2011)
You can reduce the backup time by specifying multiple files instead of single file.Only if those files are on separate drives (not separate partitions of one drive)
Actually, I have found that backing up to multiple files (not mirrors) in the same directory usually is faster, especially if the backup is going to a network share.
I'm not exactly sure why, but maybe it's just because it does a better job of keeping the available I/O bandwidth full.
September 8, 2011 at 10:02 am
Michael Valentine Jones (9/8/2011)
GilaMonster (9/4/2011)
kp_kumar (9/4/2011)
You can reduce the backup time by specifying multiple files instead of single file.Only if those files are on separate drives (not separate partitions of one drive)
Actually, I have found that backing up to multiple files (not mirrors) in the same directory usually is faster, especially if the backup is going to a network share.
I'm not exactly sure why, but maybe it's just because it does a better job of keeping the available I/O bandwidth full.
How much gain in %? Yes I know it depends, but I'd like a ballpark idea.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply