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:
- Add registry setting for MaxUserPort as shown in http://support.microsoft.com/kb/196271
- Add registry setting for TcpTimedWaitDelay as mentioned in https://technet.microsoft.com/en-us/library/cc938217.aspx
- Other than above, App server reboot or IIS service restarts are few more additional options.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)Â
6 Comments. Leave new
Which version of Windows and SQL?
SQL 2016 and Windows 2012 R2
Thank you Pinal, I’ve been experiencing this issue since a while, and nothing worked except for restarting the application server, but after reading your post, the issue was resolved by adding the registry entry.
Cheers!
should this setting be applied to the client system or server? I’m assuming client as the client is where I’m seeing the errors. And once we are in this state, the client can no longer ping other systems on the same network although the other systems can ping the client.
We are just seeing this on W2K16 SQL 2016 SP2 in an AG setting. In our case we have one node of the AG appears to be slower than the other node, at least for database backups so we prefer to be on the faster node but it doesn’t stay up for more than about a week. If we add the TcpTimedWaitDelay key it mentions a value of 30 rather than the default 120. Is this a good number or should we perhaps start below 120, say 90, and see if this is working? I assume we would need to do this on both nodes of the AG.