proc_AddWorkflow runs very slow and causes database blocking RRS feed

  • Question

  • Hello,

    We've recently started experience some strange slow page loads on our MOSS 2007 farm. We first suspected that these issues may occur when the application pools get recycled. After analyzing the IIS log files we discovered that this is not the case firstly because all 4 WFEs are having problems at the same time(even though app pools recycles are configured with different schedules on each) and secondly because page load much more slower than after an app pool recycle(sometimes after even almost 1 hour). This led as to the idea that we might have some db blocks/deadlocks. I managed to run a select on sys.dm_exec_requests last time we had this issue and noticed that we had a session running proc_AddWorkflow. This was running for half an hour and all other requests were blocked by it(or by other requests that were blocked by it and so on). I found a few similiar threads(actually just 2) but the problem was reported as being related to SP3 upgrade to MOSS, which is not the case for us(we've already had SP3 installed since 2012). Still, they didn't provide any resolution other than restarting the database services.

    Any suggestion will be greatly appreciated!




    Thursday, December 3, 2015 7:43 PM


  • It turned out that the workflow history list was causing the issues, ~14 million records in the UserData table pertained to this list, cleaning up the list fixed the issues.
    • Edited by Adrian Ganea Sunday, September 18, 2016 2:06 PM miss spell
    • Marked as answer by Adrian Ganea Sunday, September 18, 2016 2:07 PM
    Sunday, September 18, 2016 2:06 PM