Failed to establish connection error

[Mike Mestemaker]

In the middle of normal use, Vault started refusing connections. The log has the following entries:

Code: Select all

----4/23/2007 2:34:22 PM     mmestemaker--10.2.4.71(10.2.4.71)--SSL Disabled	Vault Failed to establish a connection to the database. 
----4/23/2007 2:34:22 PM     mmestemaker--10.2.4.71(10.2.4.71)--SSL Disabled	System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
   at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
   at System.Data.SqlClient.SqlConnection.Open()
   at VaultServiceSQL.VaultSqlConn.OpenConn()
   at VaultServiceAPILib.VaultServiceAPISystem.GetOpenSqlConn(HttpApplicationState has, VaultLoginInfo vli, VaultSqlConn& conn)    at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
   at System.Data.SqlClient.SqlConnection.Open()
   at VaultServiceSQL.VaultSqlConn.OpenConn()
   at VaultServiceAPILib.VaultServiceAPISystem.GetOpenSqlConn(HttpApplicationState has, VaultLoginInfo vli, VaultSqlConn& conn)

I'm trying now to stop and restart SQL server, but that puts the db in recovery mode. Hopefully it'll come back soon.

What does this mean and what should I do about it in the future?

[Mike Mestemaker]

It came back from recovery mode, but I still get a failed connection problem. Rebooting the server and crossing my fingers.

[Mike Mestemaker]

After the reboot, it went back into recovery mode and now I'm stuck waiting. The db is 5gb and the log file is 26gb, so I'm guessing this is going to take a while. This is really frustrating how long it takes to recover from something like this; I feel like I'm in the dark and have no real comfort that what I'm doing is going to make things better or worse.

[lbauer]

Regarding the SQL server recovery, you will need to allow time for SQL to recover. If your log file is getting very large, you may want to do a backup and truncate the log file. Another option is to put SQL Server in simple recovery mode, which will keep the log file from growing too large. This means that your backups should be done frequently, to minimize data loss in case of a failure.

To remedy the pool size error try this:


If that doesn't help, I'd suggest adding "Max Pool Size=x" to your web.config in the Connect String line. Example:

<add key="ConnectString" value="Application Name='SourceGear Vault Server'; Connection Reset='true'; Server=(local); Database=sgvault; Max Pool Size=x; Integrated Security='true'" />

"x" could be the total number of connections you expect your team would have; maybe add a few more just in case. The default size is 100, so perhaps a number like 150 would accommodate the number of connections.

If a user is running multiple client instances, then each would be a connection.

[Mike Mestemaker]

To remedy the pool size error try this:


If that doesn't help, I'd suggest adding "Max Pool Size=x" to your web.config in the Connect String line. Example:

<add key="ConnectString" value="Application Name='SourceGear Vault Server'; Connection Reset='true'; Server=(local); Database=sgvault; Max Pool Size=x; Integrated Security='true'" />

"x" could be the total number of connections you expect your team would have; maybe add a few more just in case. The default size is 100, so perhaps a number like 150 would accommodate the number of connections.

My team is less than 15 people; one or 2 might be using more than one machine, but it's unusual. Any idea how it's possible we've exceeded 100 open connections in that environment?

Thanks for the suggestion, I'll adjust the web.config and see how it goes.

[jclausius]

Do you have any third party applications accessing Vault - CruiseControl.NET, Draco.NET, any other vendor tool? Are you using Vault's Shadow Folders?

Sometimes there are other applications accessing the Vault Server which in turn would cause other open connections.

[Mike Mestemaker]

Do you have any third party applications accessing Vault - CruiseControl.NET, Draco.NET, any other vendor tool? Are you using Vault's Shadow Folders?

Our build machine does a twice daily batch GET from Vault using a batch file we've written. Besides calling the GET command, is there something we should be doing to make sure connections are closed after it's done?

[jclausius]

Connections should be closed automatically. You only have open database connections during concurrent Vault user requests.

You may want to monitory the Vault Server Log to see if you can spot any unexpected usage behavior.


You can also monitor SQL Server's activity to see connections to the sgvault database.


Is it possible early on the database was in recovery mode or somehow not available? If so, then as people tried to log in, the number of attempted connections would build until you hit the max pool size.