Can high number of connection cause SQL Server to refuse connection?

sp_configure “user connections” will decide the maximum number of simultaneous user connections allowed on SQL Server.Default value for user connections is 32767. So unless we hange the value it wouldnt cause problems.

Connections will be refused if we exceeded max worker threads. Look at the SQL Server error log and identify if there are deadlocked scheduler dumps. If there is follow the steps in http://mssqlwiki.com/2010/06/15/how-to-analyze-deadlocked-schedulers-dumps/ identify on what resource your sessions are waiting (Not excatly session. It is thread or session with threads associated with it).

When you have the issue look at sysprocesses for session with Kpid and identify where they are waiting and check if the active sessions (Sessions with KPID) exceeded max worker threads.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s