locked
Serious Blockages on the SQL server causing SCSM 2010 to be unusable RRS feed

  • Question

  • Hi All,

    If this is in the wrong forum, please let me know and I will re-post / move...

    We have had an issue which has caused a great deal of blocking in the SCSM database.  The processes that have been casing the issue seem to have been;

    ServiceManager.dbo.p_EntityChangeLogSnapshot;1
    ServiceManager.dbo.p_RelatedEntityChangeLogInsertion;1

    We have also had issues with the AD connectors over the weekend, but are not sure if this is the cause of the issue or has been caused by the problem.   We have a  powershell script which runs nightly to enable, run and disable the AD users connectors but whilst this has enabled them, it does not seem to have disabled them (we are looking into this) and one has taken over 2 days to complete (normally take 10 mins tops)- All ADUsers connectors are now disabled, the SQL server services have been re-started and everything is back to 'normal'.

    So the question is has anyone out there had similar issues and/or know a possible cause / solution to this issue.

    Thanks in Advance, Ed 


    Ed

    Monday, June 25, 2012 8:39 AM

Answers

  • Hi,

    I was running into a similar situation.

    I was running a sp_who2 on my SQL server and I was seeing a lot of "blocking" happening.

    My workflows were not kicking off properly and a few other issues.

    After much troubleshooting and some help from MS. It was determined to be a corrupt workflow, what had actually happened to me, is that my MP containing my Workflow was corrupted.

    So, I stopped the "Health Service", disabled all the workflows and slowly started re-enabling workflows and then starting the "Health Service" again and slowly started testing like that and I was able to determine that any workflow or template within a set MP was causing the SQL Blocking. As a result, I re-created the workflows and Templates from scratch and now I have no issues at all.

    Slow and painful, but well worth it now.

    Hope this helps your troubleshooting.

    F.


    As long as you learn something new every day, the day is not wasted.

    • Marked as answer by EdShep Tuesday, June 26, 2012 7:26 AM
    Tuesday, June 26, 2012 6:39 AM

All replies

  • Hi,

    I was running into a similar situation.

    I was running a sp_who2 on my SQL server and I was seeing a lot of "blocking" happening.

    My workflows were not kicking off properly and a few other issues.

    After much troubleshooting and some help from MS. It was determined to be a corrupt workflow, what had actually happened to me, is that my MP containing my Workflow was corrupted.

    So, I stopped the "Health Service", disabled all the workflows and slowly started re-enabling workflows and then starting the "Health Service" again and slowly started testing like that and I was able to determine that any workflow or template within a set MP was causing the SQL Blocking. As a result, I re-created the workflows and Templates from scratch and now I have no issues at all.

    Slow and painful, but well worth it now.

    Hope this helps your troubleshooting.

    F.


    As long as you learn something new every day, the day is not wasted.

    • Marked as answer by EdShep Tuesday, June 26, 2012 7:26 AM
    Tuesday, June 26, 2012 6:39 AM
  • Thanks Fletcher...

    I will mark as the answer and look into it.  Maybe this is another reason to look at re-implimentation when we move up to 2012 as opposed to an in place upgrade.

    Thanks again for the speedy informative response.

    Ed ;)


    Ed

    Tuesday, June 26, 2012 7:25 AM
  • Excellent and good luck, like I said, slow and painful, but the results are amazing :)


    As long as you learn something new every day, the day is not wasted.

    Tuesday, June 26, 2012 7:28 AM