Some Workflow Manager useful database queries
Following my last post, I was thinking that would be a good idea sharing some other queries that you can use on your Workflow Manager Farm databases.
With this in your hands you can, sometimes, identify some potential issue that will need your attention or in some other cases will provide you with a little more of information when you are troubleshooting an already existing issue.
For example, lets suppose that you want to know all the Custom Workflows that you have in your Sharepoint and are hosted on your Workflow Manager Farm, you could run the below query and you will get your answer.
SELECT wa.ClrName, count(*) as num FROM ([WFInstanceManagementDB].[dbo].[Instances] i join (select w.Name, ClrName from [WFResourceManagementDB].dbo.WorkflowServices w join [WFResourceManagementDB].dbo.Activities a on w.ActivityId = a.ActivityId) wa on i.WorkflowName = wa.Name) group by ClrName order by num desc
Another piece of useful information could be if you need to get all the Workflow Manager relevant configuration, you could get that by executing the below query.
SELECT * FROM [WFManagementDB].[Store].[ServiceConfig]
There is a very known issue, in case you do not have the WFM CU3 installed on your WFM Farm, that is characterized by a huge number of deferred messages on your Service Bus databases that eventually will crash your WFM Farm completely, so this one is one those queries that could be a life saver.
With this we can see how many Deferred Messages you have one your database, depending the scenario if you start seeing numbers above 500 000 and growing, you should be scare and start looking into it.
Select count(*) as totalDeferred from [MessageReferencesTable] Where state=2
You can even complement the result of the above query with the next one, so you can get an idea of how those Deferred Messages are grouped by a Session Id, that will give you an insight about the problematic Session where you should focus your efforts.
SELECT T2.SessionId,t1.WorkflowName,t1.WorkflowStatus,t2.state, count(*) as total FROM [WFInstanceManagementDB].[dbo].[Instances] T1 inner join [SBMessageContainer01_Cx].[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
Another other query that could be very helpful, would be try to understand from all those deferred messages which ones are associated with active Workflows, these could potentially shown an issue where you can clearly see that the number of deferred messages associated with existing workflows is pretty different from the number of total deferred messages, leading to an issue of orphan messages that needs to be addressed.
SELECT sessionid, count (*) Total FROM [SBMessageContainer01_Huge].[dbo].[MessageReferencesTable] group by sessionid having SessionId in (SELECT [SessionId] FROM [WFInstanceManagementDB_Huge].[dbo].[Instances] where WorkflowStatus in ('Started') and sessionid in (SELECT sessionid FROM [SBMessageContainer01_Huge].[dbo].[MessageReferencesTable] group by sessionid)) order by Total Desc
As you are aware, the Workflow Manager have a certain number of system workflows that take care of cleaning up the suspended/completed workflows after the retention period expired.
The default retention period on Server is 30 days, so after that time all the expired workflows should be cleaned from the database.
So, if you suspect the workflows are not cleaned up you can check the status of System Workflows in the DB with the following query, all this instances should be running and active if not you will have a problem. .
select * from Instances (nolock) where InstanceId like '00000000-0000-0000-0000-00000000000%'
And that's it for now, hope that this helps, if I got some request maybe I can throw here a couple of more useful queries to help you get to know better your Workflow Manager Farm.
Cheers