SummaryHow to clean up the Front and Back end of SharePoint to function at its full potential.
Front end related clean upHow many items are in your ‘NintexWorkflowHistory’ lists? (should be under 5,000 items) Navigate to Http://SiteName/Lists/NintexWorkflowHistory and check how many items are present. This list follows SharePoint limitations on list sizes (typically anything more than a few thousand entries can/will cause problems). If you have a fairly large amount of items in this list, we advise that you purge some of these items to prevent timeout issues at runtime. You can run our NWAdmin operation ‘PurgeHistoryListData’ to accomplish this. More information about the commands can be found in our NWAdmin operations guide located here: Nintex Workflow 2013 (https://community.nintex.com/docs/DOC-1026); Nintex Workflow 2010 (https://community.nintex.com/docs/DOC-1027) Guide: https://community.nintex.com/community/getting-started/blog/2015/09/16/how-to-purge-large-history-list-and-dboworkflowprogress-table
How many workflow tasks are in the Task List this workflow utilizes? (should be under 5,000 items) Navigate to the task list your workflow/task action is configured to use and check how many items are present. This list follows SharePoint limitations on list sizes (typically anything more than a few thousand entries can/will cause problems. If you have a fairly large amount of items in this list, we advise that you terminate old workflows to clear out old/non-relevant tasks. Workflows that error out or are terminated eliminate their associated tasks as well.
SQL side related cleanupHow many rows and how many gigabytes is your ‘dbo.WorkflowLog’ tables in your Nintex Workflow databases (SQL)? (should be cleaned regularly) The tracking data for each action and task are stored in the WorkflowLog table when you have verbose logging enabled. Do you currently have Verbose Logging turned on? To review and purge see:https://community.nintex.com/community/getting-started/blog/2015/10/08/how-to-purge-dboworkflowlog-table
How many rows are in your ‘dbo.WorkflowProgress’ tables in your Nintex Workflow databases (SQL)? (should be under 15,000,000 rows)
A large amount of items (8,000,000-15,000,000~) in this table can cause a great many problems in workflow/tasks as Nintex records data about each action as it processes in this table for reporting/history purposes. If you have a fairly large amount of data in this table, we advise that you purge some of the data from your database to prevent SQL from timing out at runtime. You can run our NWAdmin operation ‘PurgeWorkflowData’ to accomplish this.The PurgeWorkflowData will help clean-up the dbo.WorkflowProgress table in the Nintex database which should be performed when the table reaches several million records (actual record counts depend on the environment/hardware). Please perform these actions during a maintenance window or off-peak time as they will place locks on the workflow progress table and can affect running and new workflows. More information about the commands can be found in our NWAdmin operations guide located here: Nintex Workflow 2013 (https://community.nintex.com/docs/DOC-1026); Nintex Workflow 2010 (https://community.nintex.com/docs/DOC-1027) (run PurgeHistoryListData first!) Guide: https://community.nintex.com/community/getting-started/blog/2015/09/16/how-to-purge-large-history-list-and-dboworkflowprogress-table
How high is your action count within SQL: Attempting to restart the workflow over and over can cause huge backlog issues within SQL as the Workflow instance is trying to run over and over. What happens here is the workflow instance continues to hit the workflow progress table, this causes actions to compile within SQL and puts a lot of stress on the workflow timer service that sometimes can cause the workflow not to run at all due to the compiled actions. I can provide you with a script for SQL that will allow you to view those workflows that are causing compiled actions to bottleneck your workflow. This script needs to be run against your content database where your workflow lives. Here are a few things to pay attention to, especially action count:
WorkflowName- Will provide the name of the workflow
WorkflowInstanceID- A new ID is issued every time the workflow is attempted to be ran.
SiteID: Will provide the ID number for your site you are running this from
WorkflowID: will provide the ID of the Workflow its self
ActionCount: This is the number that will determine the backlog anything over 200 is a sign of an issue. Anything over the 500 mark is a red herring and needs to be addressed immediately as this is what can be causing your workflow not to run. The list output by the command sometimes can be large and hard to read within SQL, its recommended to take a copy of the list and put it into a excel spread sheet. This makes it easier to read and identify which workflows are over that limit. Once you have identified that you case use the Guide i provided below "Identifying workflows potentially causing bottlenecks" will be the section you want to follow as it contains the script needed for SQL, as well as the NW admin command that you will need to run from a PowerShell sessions with administrative privileges to clear the backlog. When deciding which workflow data to purge, target deleted sites, workflows with errors, and workflow instances that have created surplus records. Please see the following article for an explanation of how to address this issue https://community.nintex.com/docs/DOC-1218
Workflow Status table:
--WHERE i.State = '2' --'Running'
--WHERE i.State = '4' --'Completed'
--WHERE i.State = '8' --'Cancelled'
--WHERE i.State = '64' --'Error'