locked
Hard delete of entities not happening! What gives? RRS feed

  • Question

  • My solution deletes entities out of CRM once they reach a certain point (the historical information is maintained in a data warehouse).
    Lately we've been suffering from performance issues in CRM so we have started to investigate the situation.  First, there were no indexes application specific indexes
    on any of our custom CRM entities.  I used SQL tuning wizard to create them (50 were added btw).  Then I started looking at the tables and noticed that nothing had been deleted.  The DeletionStateCode on the objects that should have been deleted is set to 2, but I don't see anything in the SPs that would delete the object.

    Do I need to write my own stored proc and schedule it to finish the hard delete?  Is this normal, or have I done something wrong?


    Jake Watkins EAI Architect - BizTalk, MOSS, Dynamics CRM, InfoPath, C#, WCF, WF, WPF, etc http://www.guerillaprogrammer.com/jakew
    Friday, July 17, 2009 2:03 PM

Answers

  • --for clarification - I'm joking about manually deleting stuff out of the DB.  There are too friggin many dependicies running around between objects/view/tables to be absolutely sure it is going to go down the way I expect. 

    We bumped up the timeout for the deletion server and the rows got deleted.  Good.

    I've read over the linked kn article (Performance is slow if the AsyncOperationBase table becomes too large in Microsoft Dynamics CRM 4.0) and the AsyncOperationBase table appears pretty big.  It appears that there are close to a million rows in the table waiting to be dumped.  the suggested clean up query is running right now after I added the suggested indexes.  I'll be interested to see how long it finally takes to clean up and what impact it has on overall performance.

    I really apperciate the help.
    Jake Watkins EAI Architect - BizTalk, MOSS, Dynamics CRM, InfoPath, C#, WCF, WF, WPF, etc http://www.guerillaprogrammer.com/jakew
    Monday, July 20, 2009 5:23 PM
  • Jake -

    How's this going? - Has your AsyncOperationBase table been cleaned up?

    I'm interestedin knowing whether that was a symptom or a cause of the deletion timeouts. -

    Also - a fix was included in the more recent rollups that helps with this - but you'll need to make a small registry change to activate it. -- http://support.microsoft.com/default.aspx/kb/968755/

    Note that it's only proactive, it doesn't clean up records that are already there, you may need to repeat the cleanup script as before to get a clean db going forward. 

    Scott.
    Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell
    Monday, July 27, 2009 3:42 PM
    Moderator

All replies

  • The CRM Async service should be deleting all the records that are marked for deletion.

    Make sure that the async service is running.
    Deletions i think are scheduled once a day so you might have to wait if you just delete an entity for it to be removed from the database.
    If everything is ok and deletion are still not happening. It might be that there are so many records to delete on one of the tables that the async service is timing out and failing to process the rest of the tables.

    Hope this helps
    • Proposed as answer by Wael HamzeMVP Friday, July 17, 2009 2:24 PM
    Friday, July 17, 2009 2:10 PM
  • Just looked an indeed there are errors being logged by the async service about a sql timeout.

    So what should be done?
    I'm tempted to manually delete the stuff, but I'm concerned that doing so could leave the database in a screwed up state.  Does Microsoft provide any tools to help out with this situation?  And how do customers get here in the first place?
    Jake Watkins EAI Architect - BizTalk, MOSS, Dynamics CRM, InfoPath, C#, WCF, WF, WPF, etc http://www.guerillaprogrammer.com/jakew
    Friday, July 17, 2009 3:39 PM
  • As far as i am aware Microsoft don't provide any tools to fix this issue.

    Depends also which table is timing out. If it is AsycOperationBase then this article might be helpful to try and clean it up : http://support.microsoft.com/default.aspx/kb/968520

    If it is another table. I think it is safe to do the job manually. If there are any references to another tables then the delete will error and you will have to delete records from other tables first.

    You can also look at the relationships diagrams for entities in the SDK to check links between entities before you write your scripts.

    Hope this helps.

    • Proposed as answer by Wael HamzeMVP Friday, July 17, 2009 4:13 PM
    Friday, July 17, 2009 4:13 PM
  • I would encourage you to not try and manually hard-delete the records without working with Microsoft support -

    The more critical issue is why the job is timing out, that should be addressed first and then look at the symptom of the deletes.

    The asynch cleanup script referenced by Wael is a good tool to have around and it may be a help in this situation, but you might also consider at least temporarily increasing the SQL timeout in order to get over the backlogged deletes.

    Have you tried SQL Tracing when the asynch delete job runs to see if you can identify what specifically is timing out?

    By the way - you can use the ScaleJobUpdateEditor to set the time of day the bulk delete job will run - and effectively cause it to run on - demand.

    http://blog.customereffective.com/blog/2008/08/schedule-crm-re.html


    Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell
    Friday, July 17, 2009 5:06 PM
    Moderator
  • Try it!..

    Download the tool ScaleGroupJob Update

    http://code.msdn.microsoft.com/ScaleGroupJobEditor/Release/ProjectReleases.aspx?ReleaseId=676

    This tool set the time and the hour when you want to run the Deletion Service.
    Jimmy Larrauri
    Friday, July 17, 2009 6:00 PM
    Moderator
  • --for clarification - I'm joking about manually deleting stuff out of the DB.  There are too friggin many dependicies running around between objects/view/tables to be absolutely sure it is going to go down the way I expect. 

    We bumped up the timeout for the deletion server and the rows got deleted.  Good.

    I've read over the linked kn article (Performance is slow if the AsyncOperationBase table becomes too large in Microsoft Dynamics CRM 4.0) and the AsyncOperationBase table appears pretty big.  It appears that there are close to a million rows in the table waiting to be dumped.  the suggested clean up query is running right now after I added the suggested indexes.  I'll be interested to see how long it finally takes to clean up and what impact it has on overall performance.

    I really apperciate the help.
    Jake Watkins EAI Architect - BizTalk, MOSS, Dynamics CRM, InfoPath, C#, WCF, WF, WPF, etc http://www.guerillaprogrammer.com/jakew
    Monday, July 20, 2009 5:23 PM
  • Jake -

    How's this going? - Has your AsyncOperationBase table been cleaned up?

    I'm interestedin knowing whether that was a symptom or a cause of the deletion timeouts. -

    Also - a fix was included in the more recent rollups that helps with this - but you'll need to make a small registry change to activate it. -- http://support.microsoft.com/default.aspx/kb/968755/

    Note that it's only proactive, it doesn't clean up records that are already there, you may need to repeat the cleanup script as before to get a clean db going forward. 

    Scott.
    Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell
    Monday, July 27, 2009 3:42 PM
    Moderator
  • Sorry for the slow response.

    We did two things:
    1) the scripts that were described helped some, but they did not clean up everything
    2) we increased the timeout for the CRM async service which did solve the problem

    The end result was the CRM returned to normal expected performance (ie it's a web app, so its like Amazon).

    The big lesson to me was to pay attention to the database.  For other n00bs:
    1) CRM does not make indexes for you.  run sql tuning wizard and make some
    2) Monitor the DB size and make sure deleted entities are being deleted.
    3) Monitor the IIS logfiles and watch the response times to catch performance trends early
    4) script 2 & 3 to make your life easy.

    Jake Watkins EAI Architect - BizTalk, MOSS, Dynamics CRM, InfoPath, C#, WCF, WF, WPF, etc http://www.guerillaprogrammer.com/jakew
    Friday, August 28, 2009 2:21 AM