Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

About six months ago my vendor installed SQL Server 2008 R2 into a clustered pair of servers, DB01 and DB02.

I've been logging on, periodically, to DB01 to audit some of the data contained in the database instance.

I've been using SSMS the whole time, and for six months there have been no problems.

Suddenly yesterday, I am unable to connect using SSMS.

I get the following error:

Cannot connect to DB01.

Additional information:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)(Microsoft SQL Server, Error: 53.)

This is very confusing, very little has changed since the last time I've logged in to SSMS.

TCP/IP is enabled on SQL Server, as are named pipes.

One thing I know has changed is that for the CLIENT machines that hit it, the TcpTimedWaitDelay parameter has been set to 30, and the MaxUserPort has been set to 10000.

But that's on the client machines only.

Could there be an issue arising from from these parameters not being mirrored on the database servers?

Could there be an issue arising from these two boxes sharing an IP for the clustering? The database is normally accessed through a shared IP, where DB02 takes the IP if DB01 fails.

I've considered restarting the SQL Server service but that scares the bejeezus out of me.

What if this issue spreads to the application currently using the databases?

share|improve this question

migrated from stackoverflow.com Jul 27 '13 at 13:26

This question came from our site for professional and enthusiast programmers.

    
If your app is still connecting to the server correctly then I wouldn't consider re-starting the service, as the issue is likely on the workstation you're running SSMS from. Try connecting specifically using TCP/IP (not named pipes) and see if the same issue occurs, there is an option to select the protocol under the connection properties tab of the connection dialog window. –  Kevin Jul 23 '13 at 17:03
    
TCP/IP connections yield this: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: 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.) (Microsoft SQL Server, Error: 10055) –  Garrett Johnson Jul 23 '13 at 17:42
    
There's a couple of possible solutions here: blogs.msdn.com/b/sql_protocols/archive/2009/03/09/… –  Kevin Jul 23 '13 at 17:58
    
Could this be a port exhaustion problem? I know my MaxUserPort is still 5000 on the server, and netstat reports 19,000 connections sitting around in the TIME_WAIT state. EDIT - Yeah, Kevin, I found that page right before I posted the above in parallel. It's smelling more and more like garden-variety port exhaustion, probably caused by me restarting all the client boxes (which are all actually servers with a slew of connections). Stick that in an answer, Kev, so I can make it the right answer. –  Garrett Johnson Jul 23 '13 at 17:58

1 Answer 1

up vote 3 down vote accepted

As I posted in the comments, a couple of possibilities exist for that error, mainly lack of memory for additional TCP connections (generally in conjunction with 32-bit OSes), or lack of TCP ports. Both issues (and possible solutions) are outlined here on the SQL server protocols blog:

http://blogs.msdn.com/b/sql_protocols/archive/2009/03/09/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.aspx

share|improve this answer
    
Yup, that was the stuff. –  Garrett Johnson Jul 24 '13 at 4:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.