workflow_Task_Queue_data table size very large. Using SQL Express database.

asked on October 6, 2017

I have a workflow database that is growing out of the size that is supported by SQL Express. I have reduced the days the logs are kept but it appears that it is going to continue to grow. I have some workflows that are very complicated and may be taking up the space however they have been working for months before this.

I need to know what I can do to reduce the size so I can continue to use SQL Express. Or determine if this is how its going to be and recommend to the customer to move to Full SQL.

The customer is looking at reports run from the forms database but are not looking at anything from the Workflow database.

Table Sizes.PNG (218.78 KB)

| Download

0 0

replied on October 10, 2017

Hello,

I have been working with Tech Support on this. I don't have many active workflows. My Persisted Instances are in the low hundreds. I do have a very complicated workflow that updates a website however no running instances of it. I suspect the data is stored as completed data in the database but it seems there really isn't a good way to clean it out.

My next step if the customer OK's it is to export my workflows and create a new Workflow Database. This of course well kill any running workflows and we will have to attempt to restart them.

I have adjusted the Advanced Server Options as shown a few days ago but as of yet the size of the database has not dropped.

Advanced Server Options Current.PNG (141.15 KB)

| Download

0 0

View 1 previous reply

replied on October 10, 2017

You should be able to tie it back to the workflow and instances it belongs to by joining the table on instance_id to the search_instance table:

select count(a.task_id)
	, a.instance_id
	, a.activity_name
	, b.workflow_id
from workflow_task_queue_data a inner join search_instance b
  on a.instance_id = b.instance_id
  group by a.instance_id, a.activity_name,b.workflow_id

Maintenance options are unlikely to apply here since the instances with open tasks would be still in progress and not subject to cleanup.

0 0

replied on May 10, 2022

After you find out what workflow they tie back to, what would the next steps be?

0 0

replied on May 10, 2022

You take a look at what the workflow definition is doing. Usually a low number of instances with a large number of tasks/activities indicates an inefficient way of processing large data sets (entries, SQL tables, etc).

Since information stays around for the duration of the workflow instance plus the retention period, the size of the database can be affected if you run large workflows.

For the record, setting the retention for instance data to 1 day may make it hard to investigate an issue like this if the instance details are gone before you get a chance to take a look.

Another thing to note is that SQL does not shrink databases on its own, so even if the data is purged from the database, the size on disk will not change.

0 0

replied on May 10, 2022 • Show version history

What are the dbo.workflow_task_queue and dbo.workflow_task_queue_data tables used for? How/why is information inserted and removed from the tables?

0 0

Question

Question

workflow_Task_Queue_data table size very large. Using SQL Express database.

Replies

Sign in to reply to this post.