Fixing Sql Sever 2005 Maximum number of user connections

Okay… I was playing around with SQL Server 2005 for use with ASP.NET 2.0 WebParts. I was reading a great article on how to change the connection requirement from SQL Server Express Edition to be Regular old SQL Server 2005 (I didn’t install SQL Server Express Edition… it seemed like overkill when I already had SQL Server 2005 Standard Edition installed). I had a moment of indiscretion where I set the maximum number of concurrent connections to 1 (the default is 0, unlimited). This resulted in my being able to connect to the database with Microsoft SQL Server Management Studio, but I was getting errors when I tried to view the properties on the server or view pretty much any meta data on any of the databases. Whoops! So, in reading the log file (located in C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG in a default installation) it stated "Could not connect because the maximum number of ‘1’ user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed.". In investigating how to resolve this issue, I found I could close all other connections and connect to the database using, what used to be called Query Analyzer,  Database Query Engine (Within SQL Server Management Server – File > New > Database Query Engine). Running the following command got me up and running again.
 
 

sp_configure ‘show advanced options’, 1;

go

reconfigure

go

sp_configure

‘user connections’, 0

go

reconfigure

Advertisements

12 thoughts on “Fixing Sql Sever 2005 Maximum number of user connections

  1. Thanks for the post. I took the exact same steps to break my setup. Your post helped me get things fixed.

  2. Thanks a bunch for the post…We were gonna do a SQL SERVER 2005 reinstall if not for this command.

  3. Thank a bunch! It has helped me a lot to solve that kinda weird issue… I could not imagine how it was happened – yesterday the server was fine and working well, but today the number of concurrent connections put into effect somehow… Finally I were lucky and found your troubleshooting guide!Appreciate your tech background & experience!

  4. No problem. I’m glad the information was useful.

  5. Hi mate
    Your post really helped me in sorting this issue.
    I had set max no of connections to 1 and after this i was not able to access any dtabase object nor reset the max connections property to 0 through management studio UI.But you stored procedure did the work and I am back in business.

  6. This looked promising but it did not help in the end. Since you open up SQL Server Management Studio, that is the one connection. It is now used up. If I try to go to DataBase Engine Query I get the same error –
    which in the log says one connection allowed.
    How do I get out fo this. This is crazy.

    • Running a query from within SSMS should only be making the one connection (unless, of course, you are also running another application that you are unaware of that is making an additional connection) if you make the connection and run the query above. If this doesn’t work, you could also run the query through sqlcmd. You may want to restart SQL server to ensure that any other open connections are closed. Some common culprits of maintaining open connections are: a custom database connected application, Visual Studio’s Server Browser.

  7. Jason, you the man today! This is one of the most beautiful scripts I have ever found. There seems to be no other way to get around the 1 connection that is always in use by the connection you have no way of identifying. May it live on in infamy.

  8. Thank you! I just spent hours trying to figure out what was up and this fixed it. I did have to shut down all the other SQL Server-related services first though because one of them was apparently using my 1 connection. 😎

  9. I have the same problem in SQL 2008 and can’t execute the proposed script with
    SQL Server Management Server – File > New > Database Query Engine

    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.) (.Net SqlClient Data Provider)

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