I have a very complex stored procedure in my SQL Server 2008 R2 that inserts large amounts of data into several tables of a database.
A copy of this procedure runs fine on several other servers. I usually use a job in the server agent to run the procedure, but when I do this on my dev server, the job fails, displaying a snippet of the code in the log, but little else.
When I execute the stored procedure outside of the job, the activity monitor displays the proces as Task state: "Suspended", command: "Awaiting command" and waittype alternating between "IO_Completion", "PAGEIOLatch_SH" and blank.
This page suggests there might be something with memory allocation, but there is still 9gb left on the disk writing the data and logs (not much, but it should be enough).
Does anybody know what the wait type IO_Completion means in context of executing a stored procedure?
When I had canceled the running stored procedure, it did print an error that was easily resolved. It seems a lack of memory prevented the server from returning the error properly, causing it to get stuck in the 'awaiting command' wait state.