Slow Full backup in SQL SERVER 2008 R2

  • Hi every one,

    We have database(4.6TB) in SQL SERVER 2008 R2.On weekly bases we take full backup from this database.It used to take 7-8 hours for the full backup.but now It is running for 21 hours and still it is not completed.How can I check what is the problem?

    Thanks

  • Do you take tape backup? What are the wait types your backup session is hanging on? Check with your backup team to verify the backup throughput (mb/secs).

  • Aspet Golestanian Namagerdi (12/9/2012)


    .It used to take 7-8 hours for the full backup.but now It is running for 21 hours and still it is not completed.How can I check what is the problem?

    Check the whether database have grown in recent past ?

    Are you using same disk/storage?

    I/O utlization? but for this you need to have some historical data for this

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Here is the waittypes:

    waittime lastwaittype

    58608032 ASYNC_IO_COMPLETION

    51 BACKUPIO

    0 BACKUPBUFFER

  • and I am using disk for backup operation.

  • local disk or NAS\SAN based disk?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • It's SAN bases disk(mount point)

  • Check the throughput of the SAN system as well as configuration of HBA queue depth.

    See the virtual file stats if there are high IO read and Write stall( sys.dm_virtual_file_stats).

    Capture IO, Memory and CPU counters

    Check Memory Related Perfmon Counters

    SQLServer: Memory Manager\Memory Grants Pending (Consistent higher value than 0-2)

    SQLServer: Memory Manager\Memory Grants Outstanding (Consistent higher value, Benchmark)

    SQLServer: Buffer Manager\Buffer Hit Cache Ratio (Higher is better, greater than 90% for usually smooth running system)

    SQLServer: Buffer Manager\Page Life Expectancy (Consistent lower value than 300 seconds)

    Memory: Available Mbytes (Information only)

    Memory: Page Faults/sec (Benchmark only)

    Memory: Pages/sec (Benchmark only)

    Checking Disk Related Perfmon Counters

    Average Disk sec/Read (Consistent higher value than 4-8 millisecond is not good)

    Average Disk sec/Write (Consistent higher value than 4-8 millisecond is not good)

    Average Disk Read/Write Queue Length (Consistent higher value than benchmark is not good)

    Regards,

    Ramasankar,

    MCTS,MCITP Sql server 2008.

Viewing 8 posts - 1 through 7 (of 7 total)

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