How to check if there are a great deal of deferred messages in ServiceBus container DB generated by your workflow

  • Article

The problem:

Your workflow may stop working with the following exception which usually indicates a great deal of messages accumulated in ServiceBus container database.

Microsoft.Workflow.Client.WorkflowQuotaExceededException: Cannot start more instances because the size of the topic has exceeded the quota limit

 

Troubleshooting:

First, you should check the number of rows in ServiceBus container database MessageReferencesTable. If there are millions of records in this table, there please further check if most of the records are with State = 2 (i.e: deferered message).

If they are, then execute the following SQL query to figure out those deferred messages belong to which session id and workflow:

SELECT  T2.SessionId,t1.WorkflowName,t1.WorkflowStatus,t2.state, count(*) as  total

  FROM [WFInstanceManagementDB].[dbo].[Instances] T1

  inner join [SBMessageContainer01].[dbo].[MessageReferencesTable] T2 on T1.[SessionId] = T2.[SessionId]

    group by T2.SessionId, t1.WorkflowName,t1.WorkflowStatus,t2.state

    having t2.state = 2 order by total desc

 

Resolution:

If it's a SharePoint site, you need to stop the problematic workflow and undeploy it to make sure no more records are generated in the database.

Then:

  1. Try to remove the scope with Remove-WFScope cmdlet. https://msdn.microsoft.com/en-us/library/azure/jj193255(v=azure.10).aspx
  2. If Remove-WFScope fails with timeout error, please contact Microsoft support to purge the messages with MsgCleanup tool(internal only).
  3. If MsgCleanup tool also fails with SQL timeout errors because there are too many records in the table, then you may consider manually delete the data in SQL. However please note this is not officially supported by Microsoft. To do a manual cleanup, you can: 1) Use DELETE query to remove the rows belong to problematic session id directly. 2) Create a Temp table, INSERT good records(belong to other session ids) into it. Then TRUNCAT original MessageReferencesTable and INSERT the records in Temp table back to it.

Hope the information helps.

WenJun Zhang