Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

MAXIMUM NUMBER OF USER CONNECTIONS REACHED

Posted by Hendry chinnapparaj on February 10, 2012

Problem:- you get the Following error in sql server 2008 database
2012-02-10 09:17:24.45 Logon Could not connect because the maximum number of ’10’ user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>]

Diagnose:-
Open a SQL Query session in MS SQL Server Manageent Studio
Execute sp_configure with no parameters displays all configuration options
use KOJ_Marketing_new;
go
RECONFIGURE;
EXEC sp_configure;
otherwise go specific for this parameter
use KOJ_Marketing_new;
go
sp_configure “user connections”
go

name minimum maximum config_value run_value
user connections 0 32767 10 10
It means that SQL Server is configured to accept only 10 connections. This can be changed using sp_configure ‘user connections’, 0. By default SQL will have that option set as 0 hence it accepts unlimited connections. Here someone has changed the default vaul, hence the error in this case.

Solution:-

Exit the SQL Query session and then start it in SINGLE user mode ( can do this using the studio under the properties and options tab) and try to establish a connection at the earliest and then change the options using sp_configure then Restart the Instance in MULTI-USER mode.
use KOJ_Marketing_new;
go

reconfigure;

go

sp_configure ‘user connections’, 0

go

The SQL above will update the config_value immediately, but the run_value will not change until the instance is bounced (as the parameter is not dynamic).
For any dynamic parameter, running reconfigure can dynamically change the run value.

Bouce the SQL Instance now and then query

Open a SQL Query session

use KOJ_Marketing_new;
go
sp_configure “user connections”
go

name minimum maximum config_value run_value
user connections 0 32767 0 0
Shalom
H

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

 
%d bloggers like this: