SQL SERVER – TCP Provider – An Operation On a Socket Could Not be Performed Because the System Lacked Sufficient Buffer Space or Because a Queue was Full

Sometimes, I am contacted by blog readers via email and trust me, I get to learn something new. One of my blog reader contacted me for below error about buffer space.

TCP Provider, error: 0 – An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full.

I informed the reader that this error is not a SQL Server error. Even though the above was not caused by SQL Server but it was giving errors to client while connecting so their customer said it SQL issue. I did some searching and found a KB. They informed me that they have seen the Microsoft Knowledge Base article 196271. My next obvious question was that did they make the change? They answered that how can they verify that if they have the same issue.

I asked them to use NetStat output and check if they see entries in TIME_WAIT state. As per my understanding this might be leading to buffer/port exhaustion. Here is what I could see in my server and there is no issue and hence less output.

SOLUTION/WORKAROUND

Here are the solutions which I suggested them:

1. Add registry setting for MaxUserPort as shown in http://support.microsoft.com/kb/196271
2. Add registry setting for TcpTimedWaitDelay as mentioned in https://technet.microsoft.com/en-us/library/cc938217.aspx
3. Other than above, App server reboot or IIS service restarts are few more additional options.

Reference: Pinal Dave (https://blog.sqlauthority.com)