Tidying the alfresco workflow database

During an upgrade from Alfresco to 4.2.6 for a client of ours we identified a problematic patch script which does some refactoring of data in the Activiti tables in the database. The customer system has been running for many years and the Activiti historic tables have grown large due to the fact that Alfresco never cleans these automatically. All workflow and task data is stored indefinitely in the database in the act_hi_*.

For this particular system the act_hi_detail table contained 2.1 million records (not that many for a database), however, the nasty SQL used to refactor data in the patch does not work well for a system of this size with that many workflows. The total number of Activiti processes (active and completed) were about 21000 and the patch script ran in our QA environment for many hours. Too many hours for us to have scheduled downtime, so we decided to cancel the upgrade script and find an alternate solution.

After some investigation and some trial and errors we found that when deleting a workflow using the workflow service it clears up the Activiti history tables. This can also be done from the UI per workflow after a workflow has completed. Then you have the option to delete the workflow from the workflow details page. This is nothing you do manually for 21k workflows so here comes the JavaScript console to the rescue!

var ctx = Packages.org.springframework.web.context.ContextLoader.getCurrentWebApplicationContext();
var log = Packages.org.apache.log4j.Logger.getLogger("RL_CANCEL_DELETED_WORKFLOWS");
var workflowService = ctx.getBean('WorkflowService');
logger.log("Starting cancel workflow script");
log.error("Starting cancel workflow script");

var completedWorkflows = workflowService.getCompletedWorkflows();
var limit = 5000;
logger.log("Limit is: "+limit);
log.error("Limit is: "+limit);
logger.log("Number of completed workflows: "+completedWorkflows.size());
log.error("Number of completed workflows: "+completedWorkflows.size());
if (completedWorkflows) {
	for (var i=0;i<completedWorkflows.size();i++) {
		var wf = completedWorkflows.get(i);
		if (wf.isActive()) {
			logger.log("Workflow is still active: "+wf.getId());
			log.error("Workflow is still active: "+wf.getId());
		} else {
			logger.log("Deleting workflow: "+wf.getId());
			log.error("Deleting workflow: "+wf.getId());
		if (i>=limit) {
logger.log("Finished cancel workflow script");
log.error("Finished cancel workflow script");

This script will delete all completed workflows, both old jBPM workflows and Activiti workflows. The script will most likely time out when you run it so we added some log4j logging to it as well to get a log trail in our alfresco.log.

Since we are doing some workflow maintenance here we might as well delete all Active old jBPM workflows as well (in this case we knew for a fact that they will never be completed).

var ctx = Packages.org.springframework.web.context.ContextLoader.getCurrentWebApplicationContext();
var log = Packages.org.apache.log4j.Logger.getLogger("RL_DELETE_JBPM_WORKFLOWS");
var workflowService = ctx.getBean('WorkflowService');

var activeWorkflows = workflowService.getActiveWorkflows();
var limit = 2000;
logger.log("Limit is: "+limit);
log.error("Limit is: "+limit);
logger.log("Number of active workflows: "+activeWorkflows.size());
log.error("Number of active workflows: "+activeWorkflows.size());
if (activeWorkflows) {
	for (var i=0;i<activeWorkflows.size();i++) {
		var wf = activeWorkflows.get(i);
		if (!wf.isActive()) {
			logger.log("Workflow is not active: "+wf.getId());
			log.error("Workflow is not active: "+wf.getId());
		} else if (wf.getId().indexOf("activiti")===0) {
			logger.log("Activiti workflow: "+wf.getId());
			log.error("Activiti workflow: "+wf.getId());
		} else if (wf.getId().indexOf("jbpm")===0) {
			logger.log("Canceling jBPM workflow: "+wf.getId());
			log.error("Canceling jBPM workflow: "+wf.getId());
		} else {
			logger.log("Unknown workflow type"+wf.getId());
			log.error("Unknown workflow type"+wf.getId());
		if (i>=limit) {

As a result of this maintenance job, we have about 1700 active activiti workflows and an act_hi_detail table with about 210000 rows (about 10% of the original count) and the patch went through in seconds.

This entry was posted in Uncategorized. Bookmark the permalink.

64 Responses to Tidying the alfresco workflow database

  1. I have bookmarked your website because this site contains valuable information in it. I am really happy with articles quality and presentation.

  2. Tk6iiW Thank you ever so for you article.Really thank you! Keep writing.

  3. I’m glad I found this web site, I couldn’t find any knowledge on this matter prior to. Also, operate a site and if you are ever interested in doing some visitor writing for me if possible feel free to let me know, I’m always looking for people to check out my web site.

  4. xe88 tips says:

    i so like this blogger hihih..

  5. Name says:

    comment text

  6. Alicia says:

    I really enjoy this superb article which you’ve provided for us. I guarantee this could be beneficial for the majority of the people. http://www.couponsviews.com

  7. You can definitely see your expertise within the work you write.

  8. wonderful issues altogether, you simply won a logo new reader. What would you suggest in regards to your post that you simply made a few days in the past? Any positive?

  9. dong ho fake says:

    Im obliged for the blog.Really looking forward to read more. Will read on

  10. eztv torrent says:

    This blog is really cool and besides diverting. I have picked many useful tips out of this source. I ad love to come back again soon. Thanks a bunch!

  11. Too many times I passed over this link, and that was a blunder. I am glad I will be back!

  12. This awesome blog is really awesome and besides amusing. I have discovered helluva handy advices out of this amazing blog. I ad love to visit it every once in a while. Cheers!

  13. Muchos Gracias for your blog post.Thanks Again. Much obliged.

  14. wholesale cheap jerseys ??????30????????????????5??????????????? | ????????

  15. Right now it sounds like BlogEngine is the preferred blogging platform available right now. (from what I ave read) Is that what you are using on your blog?

  16. There is evidently a bundle to realize about this. I assume you made certain nice points in features also.

  17. Your style is so unique in comparison to other people I ave read stuff from.

  18. Perfectly pent content material , appreciate it for entropy.

  19. 부커취 says:

    Thank you for your post.Really thank you! Cool.

  20. Major thankies for the blog.Much thanks again. Really Cool.

  21. click here says:

    Your style is very unique in comparison to other folks I ave read stuff from. I appreciate you for posting when you ave got the opportunity, Guess I will just book mark this page.

  22. It as not that I want to duplicate your web-site, but I really like the style and design. Could you tell me which theme are you using? Or was it especially designed?

  23. bacarasite says:

    Always so interesting to visit your site. What a great info, thank you for sharing. 바카라사이트

  24. What a great info, thank you for sharing. this will help me so much in my learning.When your website or blog goes live for the first time, it is exciting. That is until you realize no one but you and your. 파워볼사이트

  25. Wow, great post.Really looking forward to read more. Fantastic. 토토사이트

  26. Pinganillo says:

    Very good information. Lucky me I discovered your site by chance (stumbleupon). I have bookmarked it for later!

  27. Thankyou for this tremendous post, I am glad I observed this site on yahoo.

  28. R S Matka says:

    Make sure is your own, so you can maximize earnings. To earn some quick cash online try one of the many auction sites. These sites let you sell a myriad of items, both new and used. Visit here Online Matka for more information. Its totally verified and short term earning money process. So please don’t hesitate to asking question. Contact number shows on website, make a call and win.

  29. Im thankful for the post.Much thanks again. Much obliged.

  30. Very couple of internet sites that occur to become in depth below, from our point of view are undoubtedly well worth checking out.

  31. Thanks for sharing, this is a fantastic article post.Really thank you! Want more.

  32. Really informative blog article.Thanks Again. Awesome.

  33. Some really quality blog posts on this website , saved to my bookmarks.

  34. may you be rich and continue to guide other people.

  35. Thanks so much for the blog article. Want more.

  36. Wow! This could be one particular of the most beneficial blogs We ave ever arrive across on this subject. Actually Wonderful. I am also an expert in this topic so I can understand your effort.

  37. Keep up the wonderful piece of work, I read few blog posts on this web site and I believe that your site is real interesting and has got bands of wonderful information.

  38. Keep up the superb piece of work, I read few articles on this web site and I think that your web blog is very interesting and has got sets of great info.

  39. I value the post.Really thank you! Fantastic.

  40. You have provided good information and this article is too much helpful.

  41. oncasinosite says:

    wow… what a great blog, this writter who wrote this article it’s realy a great blogger, this article so inspiring me to be a better person

  42. toto365pro says:

    This post is very simple to read and appreciate without leaving any details out. Great work!

  43. Thank you so much for ding the impressive job here, everyone will surely like your post.

  44. Cheap and overpriced city sluts on sex. Verified photos. Phone numbers connected to Viber and Vatsap.

  45. Naagin is a colors tv serial. 5 seasons of this magical show have been released.

  46. Naagin is a colors tv serial. 5 seasons of this magical show have been released. thanks

  47. adobe course says:

    Good write-up. I absolutely love this site. Stick with it!

  48. zxc says:

    Speed Test : Ookla, Orange, UPC, T-mobile | InternetSpeedTest.pl

  49. Awesome article! Thanks :)

  50. gqjksadjqq says:

    hellow~~I was surprised to see your writing. This is a really useful resource~
    I would like to see these materials often~
    I will come here often. Thank you!!!


  51. This post is very simple to read and appreciate without leaving any details out.

  52. Awesome article! Thanks. Amazinng workk.

  53. says: is a private IP address used to login the admin panel of a router. 192.168.l.l is pre-specified by the router companies as the default gateway address and can be used to make different changes to the router’s settings.

  54. Francisco Horton says:

    meloxicam for dogs cost meloxicam dose conure meloxicam with kidney issues

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>