November 12, 2008 at 6:06 am
Hi,
In testing environment the connections are getting increased and not able to establish new connections.
So, How do I increase the number of connections in SQL Server 2005
Thanks,
Regards
Viji
November 12, 2008 at 7:07 am
By default the only max for connections is the maximum allowed by SQL which is 32767. If you're reaching that many concurrent connections on a dev system, I think you have problems.
What's the error returned when trying to make more connections?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2008 at 3:29 am
Hi,
In the front end they are getting IndexOutOfRangeException
TimeOutExpired. The time out period elapsed to completion of the operation or the server is not responding
and Sometimes they are not able to connect to the database itself. the same proc works some other time.
So the server may not allow new connections, due to reach into highest connections.
if there is any wrong in my understanding please clear my doubts.
Thanks,
regards
Viji
November 13, 2008 at 4:55 am
That doesn't sound like a login problem. It sounds like a performance problem with that procedure. It doesn't return quickly enough and the front end times out. It may be related to blocking as well.
How many connections does the server have? (query sys.dm_exec_sessions and look for the number of sessions with a session ID > 50)
How long does the proc take if you run it from management studio?
Take a look at the proc and see if there's any possibility for optimisation, either by changing the queries or changing indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2008 at 5:12 am
I am agreed with Gail.
It looks like performance issue. Try to find more detail finding in the errorlog.
Also perform test to find out any hardware & software bottlenack. BE CAREFUL IF YOU ARE RUNNING TRACE ON PRODUCTION. Alternatively you can run the trace from remote server too.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
November 14, 2008 at 10:58 am
HI,
Thanks for your reply.
It doesn't take much time when it runs from SQL Server.
My doubt is when we are in a testing environment how to avoid this kind of errors and what is the way to increase the number of connections when it got set to a lower limit.
Regards
Viji
November 16, 2008 at 8:04 pm
viji (11/14/2008)
It doesn't take much time when it runs from SQL Server.
Could be a parameter sniffing issue, could be a few other things. Best thing to do here is run a trace, see exactly what's running with what parameters and then try to optimise it.
My doubt is when we are in a testing environment how to avoid this kind of errors and what is the way to increase the number of connections when it got set to a lower limit.
As I said, the only limitation to the number of connections (by default) is SQL's max capacity, which is 32767. From what you've said, this error has nothing to do with the number of connections.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply