Understanding the error “An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full.”
This error message, which is associated with the Winsock error WSAENOBUFS and actually comes from Windows rather than SQL Server directly, has two well-understood root causes. However, it still stumps people today, probably because there is no single source which explains both root causes and offers solutions for both. Here is a description of the two common situations where you may see this error and quick solutions for each:
OS runs out of memory for TCP buffers
When a powerful client machine, especially one with lots of RAM, is running an x86 version of Windows, people use the /PAE switch in the c:boot.ini file to allow applications on that machine to be able to address the full range of memory. One other switch often used to give more memory to applications is the /3gb switch in the boot.ini file. The problem comes when these two are combined: the /3gb switch gives more memory to applications by reducing the amount of memory available to the OS. When it is used on a powerful machine where the applications require many OS resources, such as by opening many TCP connections, this can cause the OS to run out of memory for resources like TCP buffers. When that happens, Winsock throws the error WSAENOBUFS.
Solution: Remove the /3gb switch from c:boot.ini. The root problem in this case is memory pressure on the OS, so removing the /3gb switch will give more memory to the OS and will alleviate this problem.
OS runs out of available TCP “ephemeral” ports
When the client machine is opening many TCP connections and is running Windows Server 2003, Windows XP, or any earlier version of Windows, it may run out of TCP “ephemeral” ports. In Windows Server 2003, Windows XP, and earlier versions, Windows limits the number of available ephemeral ports to approximately 5000 across the machine. It is especially common to hit this problem for applications which do not use connection pooling.
Solution: To make more ephemeral ports available, follow the directions in this KB which describe how to create the MaxUserPort registry key: http://support.microsoft.com/kb/196271
SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights