Database Timeout Crashes Blueprism

[christiansilva]

Hello guys, 

I am having issues with databases timeouts causing crash on blue prism.  When I open the server this message appears as the blueprism has stopped working.

Some robot logs indicate that other bases that the robot queries are also timout, but in this case I believe that the application is crashing due to the timeout of its own database. It is a database with a lot of infrastructure resources so I don't understand how this problem can happen.


So I got the event viewer logs and got the following error.

BluePrism.AutomateAppCore.Resources.ResourceRunnerBase
[2020-05-02 09:04:01Z] Listener failed : failed to update resourcepc details on the database - System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=4; handshake=14998; ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Server stack trace:
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at BluePrism.AutomateAppCore.clsDBConnection.SetupCommand(IDbCommand cmd)
at BluePrism.AutomateAppCore.clsDBConnection.ExecuteReturnDataReader(IDbCommand cmd, CommandBehavior behave)
at BluePrism.AutomateAppCore.clsServer.GetResourceIdAndFQDN(IDatabaseConnection con, String resourceName, String& fqdn)
at BluePrism.AutomateAppCore.clsServer.RefreshResourcePC(String resourceName, ResourceDBStatus status, Int32 runningSessions, Int32 activeSessions)
at SyncInvokeRefreshResourcePC(Object , Object[] , Object[] )
at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)
at BluePrism.BPCoreLib.Extensions.RethrowWithStackTrace(Exception this)
at BluePrism.AutomateAppCore.MyExceptionHandlingMessageInspector.AfterReceiveReply(Message& reply, Object correlationState)
at System.ServiceModel.Dispatcher.ImmutableClientRuntime.AfterReceiveReply(ProxyRpc& rpc)
at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)
at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at BluePrism.AutomateAppCore.IServer.RefreshResourcePC(String name, ResourceDBStatus status, Int32 runningSessions, Int32 activeSessions)
at BluePrism.AutomateAppCore.clsListener.RefreshDBWithCurrentState()
ClientConnectionId:4c5f8ae8-d30f-45d1-8506-7751d32356ee
Error Number:-2,State:0,Class:11

Someone already have this kind of trouble ?

------------------------------
christian silva
------------------------------

[henrynli]

Hi Christian,

It is worth isolating problem by first checking your Database Utilization to see if there is any table having size more than 30GB, if you did not do regular archiving or housekeeping.

If really your found a table is too large and Archiving does not work, log a ticket with support and ask for House Keeping scripts.


------------------------------
Henry Li
------------------------------

[SteveWaters]

Christian;

I would reach out to Blue Prism support and get our "Housekeeping Scripts."  Maintenance of the BP database is critical to keeping it running. 

Blue Prism isn't a great reporting tool so regularly archiving data is a good idea.  Typically not more than about 7 days is required.  The tables that tend to get large and cause problems are the Session Log and Workqueue tables.  It is typically better to move the data to another database or repository and run reports from there.  Especially if it's a large active environment. 

Another thing to look for is running processes in Production using Full Logging.  Many people don't realize that's not simple logging it's more like debug logging mainly for development and should rarely be used in Production. 


------------------------------
Steve Waters
Platform Consultant
Blue Prism Professional Services
America/Chicago
------------------------------

[NicholasZejdlik]

We did have database issues ourselves at one time, for us it was causing the Get Next Item stages to timeout. Eventually, after observing the database via SQL Profiler, we found the issue to be a bad execution plan, and rebuilding indexes on the BPAWorkQueueItem table resolved it. I would personally recommend using SQL Profiler to see if there are any unusual wait times on queries. Also, checking the Page Life Expectancy on your database would help to indicate if there in fact any performance issues.

To Henry's point about table sizes, I haven't seen any evidence that suggests that table sizes would cause any kind of an issue. Our session log table at one time was over 150 GB until we trimmed it for the sake of disk space, but there was no performance change on any queries. Likewise, we have 2.5 million work queue items currently sitting in the BPAWorkQueueItem table, but it does not cause any performance issues (so long there are not thousands of items in pending). I've monitored a lot of Blue Prism's queries via SQL Profiler and they seem pretty well designed and indexed to avoid performance degradation on table growth.

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------