Post
Ask Question

Nintex Purge Data Deployment Plan - SharePoint Servers

How to purge items in a large Nintex Workflow History list

 
This applies to the following products: Nintex Workflow 2010, Nintex Workflow 2013.
 
If you have more than 5,000+ records in your Nintex workflow history list, you may need to review other options to purge these items as the GUI may fail its purge operation.This PowerShell script allows you to safely target and purge items from a workflow history list to keep the size down and manageable.
 

Using PowerShell to purge items

 
This script utilizes paging and indexing to specifically target each item and delete it. Paging helps throttle the traffic to your SQL server down by only deleting x number of items at a time before it rests and starts again. Indexing enables the targeting of items without the performance overhead of enumerating and/or querying a large collection of items.
 
Note
This script will not purge or delete data using any state, this will just remove everything from given date and time.
  1. #Load Assemblies    
  2. Add-PSSnapin Microsoft.sharePoint.Powershell -erroraction SilentlyContinue    
  3. [void][System.reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")    
  4. #Set site and list    
  5. $web = Get-SPWeb "http://your site"    
  6. $list = $web.Lists["NintexWorkflowHistory"]   
  7. #### Number of days to keep WF history (-60 = last 60 days, -180 = 6 months)    
  8. $DeleteBeforeDate = [Microsoft.SharePoint.Utilities.SPUtility]::CreateISO8601DateTimeFromSystemDateTime([DateTime]::Now.AddDays(-37))    
  9. #CAML filters (last activity date), leave this line as it is.    
  10. $caml='<Where> <Lt> <FieldRef Name="Occurred" /><Value Type="DateTime">{0}</Value> </Lt> </Where> ' -f $DeleteBeforeDate    
  11. #Setup Query    
  12. $query=new-object Microsoft.SharePoint.SPQuery    
  13. $query.ViewAttributes = "Scope='Recursive'"    
  14. #$query.RowLimit = 2000 #Uncomment to Limit the number of rows returned. (Useful on extremely large lists)    
  15. $query.Query= $CAML    
  16. $items=$list.GetItems($query)    
  17. Write-Host "Number of items about to be deleted:" $items.Count    
  18. #!!! Delete Items !!!!    
  19. $items | % { $list.GetItemById($_.Id).Delete() }    
  20. #Empty Recycle Bin    
  21. write-host "Emptying Recycle Bin..."    
  22. $web.RecycleBin.DeleteAll()    
  23. #Dispose Thread    
  24. $web.Dispose()   
Note
On the completion of this script we need to purge the history from DBO level as well, it’s more important as the part of purring the workflow history.
 
WARNING
 
Only perform a dbo.WorkflowProgress clean up AFTER you have purged data from your Nintex workflow history lists. Not doing so will prevent you from purging items from the history list using the "PurgeHistoryListData" command unless the "-clearall" switch is used.
  • Below are the example screenshots for more details,

  • Copy the script to anyone of SP server and do the modification in site, date and List,

  • You can monitor the list data and count from SP site page and wait till this script is completing.

DBO table purge – Using Store procedure

  • Open SSMS, select the DB where the Site has been associate with Nintex DB, expand the DB and select programmatic and store procedure. Refer to the below screen shot.

Before we proceed, we can identify the current data on the DB for a particular Site collection.
  1. SELECT    
  2. COUNT (case when state = 2 then 'Running' else NULL end) as Running ,    
  3. COUNT (case when state = 4 then 'Completed' else NULL end) as Completed,    
  4. COUNT (case when state = 8 then 'Cancelled' else NULL end) As Cancelled,    
  5. COUNT (case when state = 64 then 'Error' else NULL end) as Error,   
  6. COUNT (WorkflowInstanceID) AS TotalWorkflows    
  7. FROM    
  8. dbo.WorkflowInstance    
  9. where SiteID = 'xxxx6517c-47d4-4c48-9bc8-604d50eecxxxxx'  
Once, you have identified and made a note, we are good to go with SP to purge data from DBO table.
  • Just click on ok for the store procedure page and wait until purging is completed.
  • We can also use one or more of the parameters to fine-tune the query to limit the information that is removed.


Similar Articles