Backup taking more time than usual

  • beejug1983

    SSCertifiable

    Points: 6491

    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.

  • Gail Shaw

    SSC Guru

    Points: 1004474

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • beejug1983

    SSCertifiable

    Points: 6491

    yes the data volume hasn't grown by half, and I dont have knowledge about performance monitor tool.

  • kp_kumar

    Mr or Mrs. 500

    Points: 532

    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

  • Gail Shaw

    SSC Guru

    Points: 1004474

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Malcolm Daughtree

    SSCrazy

    Points: 2373

    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

    😛

  • beejug1983

    SSCertifiable

    Points: 6491

    Only backup job is running at scheduled time.

  • Grant Fritchey

    SSC Guru

    Points: 396549

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • gorachandindia 30156

    Old Hand

    Points: 363

    Are you taking this backup in local disk or in network share drive?

  • beejug1983

    SSCertifiable

    Points: 6491

    Its SAN drive.

  • gorachandindia 30156

    Old Hand

    Points: 363

    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.

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Ask what changed to the san / network admin.

    Things just don't slow down on their own.

  • rcaber

    Newbie

    Points: 7

    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

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    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.

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    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 21 total)

You must be logged in to reply to this topic. Login to reply