Saturday, November 24, 2012 5:41 PM
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 6:35 PMAnswerer
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.
Sunday, November 25, 2012 2:36 AMAs 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.