Ever wonder where SharePoint keeps it’s “To Do List of things I should remember about currently running DelayActivities”?
Want to figure out if there are any “dead timer events” associated with any of your workflow DelayActivities that will never be delivered?
Here’s a piece of SQL that will dump out all the event deliveries that are pending or overdue.
Just connect to your SharePoint SQL Server database, adjust the name of the content database (if necessary) and run it. By default, the example dumps out entries from the last seven days. If you want to dump out the entire table, just comment out the AND in the example below (which helps explain the weird-looking 1 = 1 condition).
If DeliveryDate is more than 10-20 minutes behind (adjusted for UTC to your local time zone conversion), chances are good that your DelayActivity will never “pop” at the desired time.
The mystery is why, which only Microsoft can answer.
At least you now have a tool to help discover these “dead timers”.
Note: I convert the DateTime fields to ISO 8601 strings for easier viewing and importing into tools such as Microsoft Excel.
-- -- What DelayActivities were supposed to "pop" in the last few days? USE [WSS_Content] GO SELECT Convert(varchar, GetDate(), 120) [CurrentDateTime] ,Convert(varchar, wf.[Created], 120) wf_CREATED ,Convert(varchar, swi.[Created], 120) Created ,Convert(varchar, swi.[DeliveryDate], 120) DeliveryDate ,swi.[InternalState] ,wf.[SiteId] ,wf.[Id] ,[Type] ,[ParentId] ,wf.[ItemId] ,wf.[ItemGuid] ,[BatchId] ,wf.[WebId] ,[UserId] ,[BinaryPayload] ,[TextPayload] ,[ProcessingId] ,[ProcessMachineId] ,[ProcessMachinePID] FROM [dbo].[Workflow] wf INNER JOIN [dbo].[ScheduledWorkItems] swi ON wf.Id = swi.Id WHERE 1 = 1 AND swi.[Created] > DateAdd(Day, -7, GetDate()) ORDER BY swi.[Created] DESC