More Aggressively Grooming the JobStatus and WindowsWorkflowTaskJobStatus Tables

In some cases if you have a lot of workflows running, or workflows which run very frequently, or if the workflows have a lot of output the JobStatus and WindowsWorkflowTaskJobStatus tables can get pretty large.  Some customers are reporting sizes of up to 20-30 GB.  These tables store the output of workflows that run in the system.  Those workflows are almost always going to be successful and therefore are pretty uninteresting.  For the small percentage of workflows that fail for some reason though these tables store the status of those workflows, what time they started/ended, what the output was, and what the error was if any.  You can see this status information for workflows by selecting a particular workflow in the Workflows\Status view and then looking in the details pane:

By default Service Manager grooms these tables each night and deletes any data older than a configurable number of days in the past.  This configuration setting is stored in the database and not exposed anywhere in the console.  It’s stored on the PartitionAndGroomingSettings table.

SELECT * FROM PartitionAndGroomingSettings 

You can change the number of days to keep for running a query like this one below and changing the number to what you want.

update PartitionAndGroomingSettings set DaysToKeep = 3 where ObjectName = 'JobStatus'
update PartitionAndGroomingSettings set DaysToKeep = 15 where ObjectName = 'InternalJobHistory'

By making the number smaller you can more aggressively groom this table to keep the size smaller and also make the Workflows\Status view perform faster. The downside is that you won’t have as much history for troubleshooting purposes.  After making the change you’ll need to wait overnight for the next scheduled grooming job to run before you see a change in the size of the tables.