SharePoint 2010 Workflows Best Practice - Purge Workflow History List Items

BEST PRACTICE: Define requirements for when SharePoint 2010 Workflow History list items can expire and be purged, and implement either a manual or an automated process to periodically purge expired workflow history list items! 

UPDATE NOVEMBER 30, 2015: Clarification that this History List purge best practice is also applicable for:

• SharePoint 2010 & 2013 style workflows in SharePoint 2013
• SharePoint 2010 & 2013 style workflows in Office 365 SharePoint Online

Why?
The SharePoint 2010 built-in workflow cleanup job removes workflow history associations, but does not actually delete the items from the Workflow History list, allowing that list to potentially grow very large and unmanageable…sometimes millions of items (can severely impact site performance)!  Also when items exceeds 5,000 in the Workflow History list, query throttling can make the list difficult to view without a filtered query that returns less item results than the list view threshold.

Can I just increase the query throttling threshold or disable throttling on the list?  
The Microsoft best practice guidance is, “We recommend against changing the query throttling thresholds (or disabling throttling on a list). The default limit of 5,000 items was chosen to match the default point at which SQL Server will escalate from row-level locks to a table-level lock, which has a markedly detrimental effect on overall throughput.” 

Additional Information…
A workflow is made up of a sequence of events such as workflow initiation, task creation, and task completion. When you add a workflow, you specify a task list and a history list that workflow instances of that workflow association will use to track the events for the workflow. The task list contains items that users interact with and enter data into while interacting with a workflow. The workflow history contains key information about each event including date, status, participant, and description.

By default, Microsoft SharePoint Server 2010 runs a daily Workflow Auto Cleanup job to permanently delete workflow instances and related task entries that still exist 60 days after a workflow is completed or canceled. Workflow history items themselves are not deleted, but the entry point to view them on the status page for a particular instance of a workflow will no longer be available. As with any SharePoint list, as the workflow history and task lists grow in size, site performance may be compromised.

Best Practice Tips! 

•  Create a separate history and task list for each workflow association to distribute items across lists. Segregating each workflow to write into its own lists can help minimize total number of items in each list and may help avoid hitting SharePoint’s query throttling threshold.

•  Workflow history is not intended to be used to audit workflow events and is not necessarily secure because a user who has edit permissions on the site can update items in the history list, by default.  If auditing is crucial for workflows, such as tracking who approved a particular document and when, develop a custom audit mechanism (perhaps using a separate custom list or a site column to store the data).

WORKFLOW HISTORY LIST PURGE OPTIONS

The simplest purge solution to implement would be manually deleting the Workflow History list items on a regular basis before they surpass 5000 items.

An out-of-the-box solution approach to automate purging is configuring information management retention policy settings on workflow history lists, which allows SharePoint to regularly sweep the list and physically delete expired items.  One potential risk of this solution approach concerns long running workflow, because you might potentially delete history list items for which the workflow status is not yet completed.  However a best practice is to never design long running workflows (which pause and wait a long time for an event to occur before resuming and running to completion), so that shouldn’t be an issue!  Following is how to configure information management policy settings on the Workflow History list and have SharePoint automatically purge the list for you. 

1) Open the Workflow history list (your site url + Lists/WorkflowHistory

2) Open the List settings page, and select “Information management policy settings” in the Permissions and Management section. If you don’t see this option, you may need to enable the site collection feature, Library and Folder Based Retention

3) In the Content type policies, select the Workflow History content type, and then select “Enable Retention”. Once enabled, you will be able to select “Add a retention stage”.

Stage Properties

Event
  Time Period: Date Occurred + 120 days   (to purge any items over 4 months old)
Action:
  Permanently Delete

4) Save list policy, the expired items will be deleted the next time the timer jobs run.  

5) go to Central Administration -> Monitoring -> Review Job Definitions.

     In the list of timer jobs, select Information management policy and schedule the job to run at a particular instance of time (minutes, hourly, daily, weekly, monthly).  Then, select Run Now.

     In the list of timer jobs, select Expiration policy and schedule the job to run at a particular instance of time (minutes, hourly, daily, weekly, monthly).  Ensure previous job finishes running, then select Run Now.