April 24, 2012 at 11:20 am
Hello everyone,
I'm seeing some strange behaviour on our DEV box running SQL 2008 R2 64bit.
When I execute this statement:
Select @@MAX_Connections as Max_Connections
it returns: 32767
Several times during the day however the server stops accepting connections and I see the following message in the SQL log:
-------------------
Could not connect because the maximum munber of '20' user connections has already been reached. The system administrator can use
sp_configure to increase the maximum value. The connection has been closed.
-------------------
If my max connections is set to 32767, why does the server stop at 20?
I did see one database flooding the log with "starting up" messages and it turns out it has the auto close flag set to true which I will change. Could this in some way be adding to the connection problem? Just for my own knowledge, what is the reason someone would enable auto close to true on a database?
Thanks in advance,
Tim.
April 24, 2012 at 11:24 am
hanrahan_tim (4/24/2012)
Hello everyone,I'm seeing some strange behaviour on our DEV box running SQL 2008 R2 64bit.
When I execute this statement:
Select @@MAX_Connections as Max_Connections
it returns: 32767
Several times during the day however the server stops accepting connections and I see the following message in the SQL log:
-------------------
Could not connect because the maximum munber of '20' user connections has already been reached. The system administrator can use
sp_configure to increase the maximum value. The connection has been closed.
-------------------
If my max connections is set to 32767, why does the server stop at 20?
I did see one database flooding the log with "starting up" messages and it turns out it has the auto close flag set to true which I will change. Could this in some way be adding to the connection problem? Just for my own knowledge, what is the reason someone would enable auto close to true on a database?
Thanks in advance,
Tim.
From Books Online regarding @@MAX_CONNECTIONS:
Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server. The number returned is not necessarily the number currently configured.
Looks like your server is configured to allow only 20 connections.
April 24, 2012 at 11:26 am
In the Object Explorer, right click on the server, then click on Properties. Go to the Connections page, it will show you how many connections the instance is configured to accept.
April 24, 2012 at 11:30 am
I did see one database flooding the log with "starting up" messages and it turns out it has the auto close flag set to true which I will change. Could this in some way be adding to the connection problem? Just for my own knowledge, what is the reason someone would enable auto close to true on a database?
It could be good in single user mode or when only few users use the database. To be honest, I never found it necessary or good option.
April 24, 2012 at 12:04 pm
Hi Lynn,
I checked the connections page and maximum number of concurrent connections is set to 0 for unlimited.
Lynn Pettis (4/24/2012)
In the Object Explorer, right click on the server, then click on Properties. Go to the Connections page, it will show you how many connections the instance is configured to accept.
April 24, 2012 at 1:31 pm
What does this return:
SELECT name,
value,
value_in_use
FROM sys.configurations
WHERE name = 'user connections';
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 24, 2012 at 1:43 pm
That query returned:
user connections
value: 0
value in use: 0
April 24, 2012 at 1:47 pm
Someone may have changed the configuration to 20, then changed it back. It does not require a restart. That action would be logged. Keep looking through the sql server error log. You should see something like this if someone changed it:
Configuration option 'user connections' changed from 0 to 20. Run the RECONFIGURE statement to install.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 25, 2012 at 12:01 pm
I did find this in the log.
Configuration option 'user connections' changed from 20 to 0. Run the RECONFIGURE statement to install.
But there is no log entry where it is originally changed to 20. Based on the source of this message (spdi57) can I somehow trace what person or process made this change?
April 25, 2012 at 12:09 pm
Items like this do not show up in the Default Trace so unless you had some other trace or Extended Events session running to capture activity where you could correlate the date/time and spid# to a login name I do not think you can get back to the original person that made the change.
Curious about why you did not see the change from 0 to 20...did you scan all your error logs? or just the most recent one?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply