Oh Where Oh Where Has My Little [DelayActivty] Gone, Oh Where Oh Where Can He Be?

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s