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.
September 8, 2011 at 10:13 am
It does vary quite a bit. As an example, a Litespeed backup of a 350 GB database over the network to a NAS file share went from 4-5 hours to 2 hours using 15 output files.
Generally, there does not seem to be much gain when increasing the number of output files to more than 5.
I have implemented this a number of times in situations where the speed of the backup is very important, and went to the trouble of writing a stored procedure to do it.
I first discovered this when I was forced to backup to a compressed directory due to space limitations and had problems with the backup erroring out when the backup file was large. I tried backing up to multiple files and it fixed that problem, but I found that the backups also ran much faster.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply