June 12, 2009 at 7:25 pm
Hi,
I have a local SQL server on my laptop and I've set up some databases for development. I got an error saying
"A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)"
What does this error mean? How do I fix it? I tried to research on line but couldn't find the answer. Sometimes, when I restart the SQL server by using configuration manager, it would work again.
Thanks a lot for any help in advance!
June 12, 2009 at 9:39 pm
Hello,
One of the reasons that you can get this message is because the maximum number of concurrent connections is being exceeded.
I wonder if that is true in your case, as you mention that restarting the SQL Server cures the problem.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 12, 2009 at 10:11 pm
John, thanks a lot for your suggestions.
How do I find out the number of concurrent processes and how can kill the older processes?
Thanks again!
June 13, 2009 at 1:46 pm
Try this:
SELECT
'KILL ' + CONVERT(VARCHAR(20), session_id) + ' -- This kills "' + ISNULL(program_name, 'Probably a system process') + '"'
,*
FROM sys.dm_exec_sessions
Flo
June 13, 2009 at 3:21 pm
Hello again,
It is worth checking the setting for Maximum Number of Concurrent Connections. You can find this via SQL Server Management Studio (SSMS). Right-click on the Server in the Object Explorer Window and then Select Properties, then Connections.
You can also monitor the Connections on the Server via the Activity Monitor in SSMS. This is under the Management Node in Object Explorer. By right-clicking on an entry in the Activity Monitor you also get an option to kill the selected Process.
BTW – What edition of SQL Server do you have installed on your laptop?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 13, 2009 at 5:04 pm
I use SS2008 developer's version. Thanks again for all your time and suggestions.
It shouldn't be the concurrent issue then. I checked that I should have 10 concurrent connections. Also on Job Activity monitor, I only see three jobs when it happens:
new.Subplan_1
Rebuild Indexes
syspolicy_purge_history
Also, I am able to use the existing query window when the error happens. However, when I try to open a "New Query" window, I get the same error message box. Very strange.
June 13, 2009 at 5:24 pm
Hello,
Did you look through the SQL Server and Event Logs when the issue happens? I am hoping that as the error message says, “the Connection has been established”, then a full error will be logged, including Error Number and State, which will help in trouble-shooting.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 13, 2009 at 5:41 pm
Sorry,I am not very good at SS yet.
I found the "SQL Server Logs" under Management. Is that the "Event Log" you meant?
I did see the error message states" "Could not connect because the maximum number of '10' user connections has already been reached.... the connection has been closed[CLINET: ]"
Wow, how many connection do I need? Why 10 is not enough? It's on my local machine and I was just running SSIS. Is it every time when I run the package and it fails, it would cost a connection? Do I need to specifically close the connection everytime? How to free up a connection?
i feel that I getting close to a solution but it still not there yet. How did I even reach the 10 maximum connection?
June 13, 2009 at 7:15 pm
Hello,
I suspect your SSIS package is opening up multiple connections, causing you to exceed the limit of 10.
Is there any particular reason why it is set at 10, or can you increase the Maximum Number of Concurrent Connections to say 200?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 13, 2009 at 11:51 pm
John, you are absolutely correct! My DB connection is used in like 3 different tasks. Wow, I didn't realize that everytime it's called, it would use up a connection. And it seems when the package is done running, the connections are not closed up automatically. Not only it kept open, but also would cost my counts of connection? How come? Is there any way to force the close at end of my packages.
Yes, I can set it to 200. However, is there any explanation for my question above? Also, is there any way to tell how many connections have been currently used?
June 14, 2009 at 12:00 am
Also, my further testing shows that the first 2-3 times the package would run successfully and Execute SQL task under "OnError" event which I purposely trigger would run okay. After 2-3 times, the Execute SQL task would fail complaining couldn't make the DB connection.
If I set the connection time to 200, I suspect, it may run okay for 50 sometimes, and then i would need to reboot the SQL server to fix this connection problem. Why can't the connection be freed up by itself?
Thank you so much for all the guidance!
June 14, 2009 at 9:52 am
Hello,
SQL Server takes advantage of Connection Pooling. This means Connections aren’t always immediately closed when they go out of scope, rather they are returned to a pool and then available to be re-used. May be take a look at this MSDN article:-
http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx
I would guess the pool is set at the default of 100, so you will exceed the limit of 10, but if you increase the limit to 200, your issue may be resolved.
Here is a sample query from Books Online, which lists the users connected and how many sessions each of them have:-
select login_name, count(session_id) as session_count from sys.dm_exec_sessions group by login_name
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 14, 2009 at 10:11 pm
Thank you so much for all the help!
I will read those references.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply