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

Answer 1

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

Answer 2

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.