none
full backup takes long time, SPID satate is "Suspended"..

    Question

  • hello,

    I am trying to take full backup of a database, but if takes long time so i have run Activity Monitor and found that the process that run the backup command state is Suspended, and that Wait Type field is ASYNC_IO_COMPLETION

    Saturday, November 24, 2012 5:41 PM

Answers

  • Hello,

    ASYNC_IO_COMPLETION means that the system is waiting on outstanding IOs before moving forward. Take a look at your disk counters in perfmon and check to make sure you're not hitting their theoretical limit (if local disk) or get with your SAN/NAS administrator if you're using any type of SAN or NAS device. One more step I would take it to check sys.dm_io_virtual_file_state() to see if there is already an underlying issue that might be hinted at by the output.

    -Sean


    Sean Gallardy | Blog | Twitter

    Saturday, November 24, 2012 6:35 PM
    Answerer

All replies

  • Hello,

    ASYNC_IO_COMPLETION means that the system is waiting on outstanding IOs before moving forward. Take a look at your disk counters in perfmon and check to make sure you're not hitting their theoretical limit (if local disk) or get with your SAN/NAS administrator if you're using any type of SAN or NAS device. One more step I would take it to check sys.dm_io_virtual_file_state() to see if there is already an underlying issue that might be hinted at by the output.

    -Sean


    Sean Gallardy | Blog | Twitter

    Saturday, November 24, 2012 6:35 PM
    Answerer
  • As Sean said that waittype indicates that Disk i/O and I/O bottleneck how ever you have to look in all the way like SAN levle and
    Check the File Statistics to  see if there is a higher I/O Read and IO Write Stall for the sql server DB's fn_virtualfilestats.
    check eventlogs & errorlogs any specific errors.
    Check at the SAN level HBA drivers or anything abnormalities i,e Higher value.

    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Sunday, November 25, 2012 2:36 AM