Database periodically disconnects (MSSQL and Ignition 8.1)

Ignition
1

I am getting these faults from the logs where the database faults:

java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:142) at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:734) at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:300) at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnectionImpl(DatasourceManagerImpl.java:169) at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnection(DatasourceImpl.java:293) at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeToDatasource(AbstractDatasourceSink.java:165) at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeData(AbstractDatasourceSink.java:156) at com.inductiveautomation.ignition.gateway.history.sf.sinks.AggregateSink.storeData(AggregateSink.java:165) at com.inductiveautomation.ignition.gateway.history.forwarders.ForwarderThread.run(ForwarderThread.java:147) Caused by: java.util.NoSuchElementException: Timeout waiting for idle object at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:321) at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:428) at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:232) at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:134) ... 8 common frames omitted

Sometimes I see the active connections showing 8/8.

I wanna try to catch what’s happening (either from MS SQL or Ignition), if the db is getting slower or I’m actually having a heavier load and more queries.

2

It just means that you are trying to run more simultaneous queries than you've allowed in your connection settings. (The default is eight.)

There are many possible reasons:

  • Slow queries tying up the connections. (Many possible reasons for this, though poor indexing is common.)
  • Tag history high INSERT traffic.
  • Tag history high real-time chart traffic.

If you legitimately have a busy system, consider increasing the number of allowed connections in the pool. (If your DB is actually overloaded, this won't help--you'll get the same errors, but with the higher number.)

3

I think I can rule out #2 and #3 (real-time chart traffic, and tag history INSERT). It’s happened at a time where nobody was looking the live charts. Also, No tag was added to the historian and no changes were made in the History section of the tag editor.

How can I track (if possible at all) which queries are running while the database fault happens?

4

There's a diagnostic page that shows running queries for the connection.

5

If you're not doing it already, the recommended practice is that your DB will be running on its own machine and not the same one as the Ignition gateway.

6

They’re running on different VMs