Post reply

Slow Full backup in SQL SERVER 2008 R2

  • December 9, 2012 at 9:29 pm

    #265877

    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

  • December 9, 2012 at 11:06 pm

    #1566912

    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).

  • December 10, 2012 at 3:39 am

    #1566957

    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;-)

  • December 10, 2012 at 7:52 am

    #1567051

    Here is the waittypes:

    waittime lastwaittype

    58608032 ASYNC_IO_COMPLETION

    51 BACKUPIO

    0 BACKUPBUFFER

  • December 10, 2012 at 7:53 am

    #1567052

    and I am using disk for backup operation.

  • December 10, 2012 at 8:28 am

    #1567067

    local disk or NAS\SAN based disk?

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

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

  • December 10, 2012 at 8:30 am

    #1567069

    It's SAN bases disk(mount point)

  • December 12, 2012 at 12:05 am

    #1567713

    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