What's the best way to clean up Workflow DB?...ours is about 100Gb, and searches within WF designer are timing out. the search_activity_log table has over 170 mil records and search_entry_log has over 33 mil.
What version of Workflow are you running?
9.0
The database cleanup procedure for completed runs on a schedule in 9.0 and deletes data from the table. You can check the schedule in the Workflow Admin Console under Server Configuration\Server Timers\Database Maintenance.
You should also double-check your retention schedule under Advanced Server Option in the same Admin Console. The default is to keep workflows for 30 days after their completion. If it's been changed to keep them longer, that could account for the large number of rows in the tables you mentioned.
You could also run a statistics report from Monitoring\Statistics to see if the number of running instances matches your expectations. Large numbers may indicate a faulty starting rule.
Hi Miruna, thanks for the reply...when I try to run Monitoring/Statistics I get "A database error occured. Please see the server error log for more details. [0212-WF1]. Server log mentions a timeout error:
Message: <Exception handlingInstanceId="2f660e9f-dfe7-4678-9117-b5104f009d1f">
<Description>An exception of type 'System.Data.SqlClient.SqlException' occurred and was caught.</Description>
<DateTime>2016-05-19 16:39:16Z</DateTime>
<ExceptionType>System.Data.SqlClient.SqlException, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType>
<Message>Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.</Message>
<Source>.Net SqlClient Data Provider</Source>
It's probably best that you go through your reseller and Tech Support so we can take a closer look.
You can try running the following query in SQL Mgmt Studio against the WF Database to get the same data in the statistics report:
SELECT count([search_id])
,[workflow_id]
FROM [search_instance_log]
group by [workflow_id]
order by count([search_id]) desc
Thanks again...Found a runaway workflow...is there as script I can run to clean up all the noise it created in the DB?
Not a script, but your reseller can call Tech Support to get a utility that will do it.