Multiple Transactions In Progress Status In Queues

What to perform when multiple transactions are in-progress status in queues?

Issue Description: Multiple transactions In progress status in queues


Error Message Explanation: SQL Server remote query is a query that contains outgoing connection to a remote database. A SQL server parameter called remote query timeout is used to decide how long a remote query will take before initiating timeout message. The default remote query timeout value is 600 seconds. Setting the value to 0 will disable the timeout, so the query will wait until it is canceled.

Check the network Latency between the servers: If there is a network latency between the server and the remote SQL server, change this value to resolve the query timeout issue from the Connections tab of the Server Properties dialog box below.

Resolution:

  1. Reduce the count on the table [dbo].[QueueItemEvents]
  2. Database maintenance is recommended. In case database maintenance is not feasible, then check the hardware on the SQL server and try increasing RAM, CPU cores, and a faster disk.


As a workaround run the below to clear the statistics,

  • exec sp_updatestats

If the above does not work, try running: dbcc freeproccache .