Periodic connection failure on SQL Server 2012 AlwaysOn cluster
At least in part, this is going to be the answer found so far, and yes, it did take until last night to get anywhere at all with this problem.

Effectively we still don't really trust the network and we have engineers looking into this as we also have other complaints however despite the changes done being 100% revolving around the infrastructure and network the only thing we managed to fix and change so far was on the code end of things.

Effectively we have a piece of software that looks for files being dropped in a folder (hotfolder) and to make sure we never miss anything every ~15 minutes there is a check to see if there are any files there we're not yet processing. We have a lot of these things and they always run fine, nothing was changed in anything either.

Here is the scenario that happened;


Day we started having trouble a file was placed in a folder and processed
The same day the same file was placed there again


Now the code it self was horrible, it ran for 2-20 minutes depending and has today been rewritten quickly to run in ~20 seconds. There were two massive mistakes in the code that caused this problem;


If non of the lines in the file (it was an XML file) were meant to be processed the file would be left there. Triggering a new run every ~15 minutes (so the next problem would happen over and over again indefinitely)
Every line would call a stored procedure and use a DataReader to see if it had any result, if it did the call would move to the next line WITHOUT closing the reader


The combination of these two things (and the fact those files have 100s if not 1000s of rows) had our system crippled and on it's knees begging for the end.

Someone at the office blames 'connection pooling' and he might be right, he might not be. If i'm being honest I'm not sure why this would cause our systems to be so horribly affected (and the code causing it not to be by the way, never got an exception or problem on this piece of software. Which made it a pain to find).

So if anyone can explain why this would break our SQL server I'd be deligted, all I know is that ASYNC_NETWORK_IO as a wait type might be the biggest clue but I'm not enough of a DBA to find the culprit quickly. So again if anyone has any ideas/queries on how we could have pin pointed this I would love to give them the solution vote.