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

SQL
5 Comments

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.

Solarwinds

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.

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 tcp-wait-01

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

Solarwinds
, ,
Previous Post
SQL SERVER – AlwaysOn Wizard Error – Checking for Compatibility of the Database File Locations on the Server Instance that Hosts Secondary Replica
Next Post
SQL SERVER – AlwaysOn Availability Group Backup fn_hadr_backup_is_preferred_replica Not Working Correctly

Related Posts

5 Comments. Leave new

  • Which version of Windows and SQL?

    Reply
  • 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!

    Reply
  • 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.

    Reply

Leave a Reply

Menu