Two days in a row I came in to see Ignition and SQLServer hung up, with 29/30 active connections being used, and the query age being 12+ hours.
During some testing the other day, I saw that some query errors were coming from the default setting of 8 connections being exceeded, so I bumped it up to 30. Before that, I never saw this error, so I took it down to 25 a few minutes ago. But, I need to know what was going on. Note that I couldn’t even open a table from SQL Server Management Studio either. In both cases I restarted the SQLServer service, and it came out of it.
Some of the queries in the connection pool were very short, but three of them were update queries containing fairly long strings (3000 characters). The tables don’t have very many rows, so I didn’t think indexing was the issue.
Any ideas what would cause the connection pool to fill up like this? It seems to happen all at once, and like I say, it just started happening when I increased the active connections. Are there some other parameters I should have changed at the same time?
Oh, I’m running SQLServer Express 2008 and Ignition 7.2.8(b178).
There have been some observed issues in the past with SQL Server sockets “hanging”, though from your description I can’t say for certain that this is what is happening. In fact, if you can’t even get into SQL Server on the machine, the real issue is probably over there. A long query shouldn’t bring things down- you should be able to get into the database, query the outstanding operations, and potentially try killing them. It sounds to me like SQL Server is locking up somehow, and that is blocking all of the outstanding operations indefinitely.
On the Ignition side, all of the sockets used almost certainly have a socket timeout of 0, so they’ll wait forever. From what I can tell, it’s not possible to set the socket timeout with the Microsoft JDBC driver.
Now, even though I think the issue is more on SQL Server’s side, you may want to try upgrading the JDBC driver. Here’s how: