locked
CRM 4.0 Workflow History RRS feed

  • Question

  •  

    Is there a way to clear out the workflow history in CRM 4.0?  I can't see a way to do this.

     

    I have a client where the asyncoperationbase table has nearly 1,000,000 rows.  They don't really need that amount of history.

     

     

    Friday, May 23, 2008 8:00 AM
    Moderator

Answers

  • Yes, click on settings then on system jobs.  You can delete workflow history as well as many other system jobs.  No custom development required.
    Friday, May 23, 2008 10:49 AM
    Moderator

All replies

  • Yeah, you can through custom Development

     

    Regards,

    Imran

     

    http://microsoftcrm3.blogspot.com

     

     

    Friday, May 23, 2008 9:37 AM
    Moderator
  • Yes, click on settings then on system jobs.  You can delete workflow history as well as many other system jobs.  No custom development required.
    Friday, May 23, 2008 10:49 AM
    Moderator
  •  

    Agreed, but I have over 900,000 rows entries to get rid of! Even with 250 rows to a page in the list view that's 3600 separate select all and delete actions.

     

    I guess deleting rows directly from asyncoperationsbase table is a no-no.

     

    Friday, May 23, 2008 2:15 PM
    Moderator
  • If you look at the SDK, you can programatically bulk delete records. and you could use a product like Scribe Insight to do this as well.

     

    It's not supported to go directly to the database and delete/add records.

    Friday, May 23, 2008 2:20 PM
    Moderator
  • You could write an executable that queries for all AsyncOperations older than 6 months and deletes them.  You can then set up a scheduled task to run that exe hourly, nightly, weekly, etc.  Yes, it will be a bit of work up front but if you have a decent developer at your disposal you should be able to get this hacked out in a matter of a couple of hours.  All the executable is RetrieveMultiple, iterate through results to delete records found.  Setting up a scheduled task also takes about 5 minutes tops if you've done it before.

     

    That might just have to be something I post about...hope you don't mind if I use your idea for a post.  I'll be sure to post the code, if anyone downloads it they'll need to update the web references anyway.

     

    Anyway, I don't know when I'll get to it but look for it at http://dmcrm.blogspot.com

     

    Fronk

     

     

    Friday, May 23, 2008 10:10 PM
  • Please do use my idea for a post. I look forward to seeing your published code - thanks in advance.

     

     

    Sunday, May 25, 2008 7:01 PM
    Moderator
  •  

    The mystery deepens.......

     

    I have a table called ayscoperationsbase with nearly 1,000,000 rows

    I also have a table called workflowlogbase

     

    When I delete system jobs logs via the CRM application the number of rows in asyncoperationsbase doesn't change but the number of rows with a deletionstatecode of 2 goes up. Incidentally I've watched the size of this table over several days and the number of rows has never gone down. I expected a process to remove rows with a deletionstatecode of 2 but this isn't happening.

     

    I've managed to put together code to programmatically delete WorkflowLog entries using TargetDeleteWorkflowLog. But this only seems to delete items from the workflowlogbase table. It doesn't delete rows in the asyncoperationsbase table or change any to have a deletionstatecode of 2 .  

     

    So how do I properly clear out the asyncoperationsbase table and what is the relationship between asyncoperationsbase and workflowlog?

     

    Any tips would be greatly appreciated.

     

     

    Thursday, May 29, 2008 11:14 AM
    Moderator
  • Hello,
    I have the same problem.

    I already tried to at least get some records out of the system using the webservice, but the following code does always throw an unexpected error


    Code Snippet

    QueryExpression query = new QueryExpression();

    query.ColumnSet = new AllColumns();

    query.EntityName = EntityName.asyncoperation.ToString();

     

    query.PageInfo = new PagingInfo();

    query.PageInfo.Count = 10;

    query.PageInfo.PageNumber = 1;

    BusinessEntityCollection coll = null;

    try

    {

    coll = CrmPublicWebService.CrmServiceAccessor.Service.RetrieveMultiple(query);

    }catch (System.Exception ex)

    {

    }

    asyncoperation[] details = new asyncoperation[coll.BusinessEntities.Length];

    Array.Copy(coll.BusinessEntities, details, coll.BusinessEntities.Length);

    foreach (asyncoperation w in details)

    {

    Response.Write(w.statecode.Value.ToString() + "-" + w.statuscode.Value);

    //CrmPublicWebService.CrmServiceAccessor.Service.Delete(EntityName.workflowlog.ToString(), w.workflowlogid.Value);

    }

     

     

    can anyone get this to work?

     

    Thank you!

    Regards,

    Uwe

    Friday, June 6, 2008 2:16 PM
  • Here's the code (I wrote mine as a console application) that I used to clear out the asyncoperation base table. Perhaps it will help you debug your code.

     

    I did get errors if I tried to delete too much at one - I used dates to limit each deletion batch to around 80,000 rows.

     

    Note that this code marks the affected rows as deleted - but they never get removed by any of the CRM-related SQL jobs. I resorted to manually deleting the rows with the following:

     

    delete from asyncoperationbase where deletionstate='2'

     

    Please also note that the workflowlogbase table contains rows that depend on the asyncoperationbase table - so you should clear our that table first (using the SDK) and then deal with asyncoperationbase.

     

    Code Snippet

    using System;
    using System.Collections.Generic;
    using System.Text;
    using Workflow_Log.CrmSdk;

    namespace Async_log
    {
        class Clear_async_log
        {
            static void Main(string[] args)
            {
                // Set up the CRM Service.
                CrmAuthenticationToken token = new CrmAuthenticationToken();
                token.AuthenticationType = 0;
                token.OrganizationName = "YOUR ORGANIZATION NAME";

                CrmService service = new CrmService();
                service.Credentials = System.Net.CredentialCache.DefaultCredentials;

                service.CrmAuthenticationTokenValue = token;
                service.Url = "http://YOUR CRM SERVER/mscrmservices/2007/crmservice.asmx";

                service.Timeout = 300000;

                // set the columns to be returned
                ColumnSet cols = new ColumnSet();
                cols.Attributes = new string[] { "startedon", "name" };

     

                // Delete logs between two dates - setup the two dates

     

                // Create the on or before condition
                ConditionExpression dateCondition = new ConditionExpression();
                dateCondition.AttributeName = "startedon";
                dateCondition.Operator = ConditionOperator.LessThan;
                dateCondition.Values = new object[1];
                // Note date format mm/dd/yyyy
                dateCondition.Values[0] = "06/03/2008 23:59";
               
                           

                // Create the date greather than condition
                ConditionExpression dategtCondition = new ConditionExpression();
                dategtCondition.AttributeName = "startedon";
                dategtCondition.Operator = ConditionOperator.GreaterThan;
                dategtCondition.Values = new object[1];
                // Note date format mm/dd/yyyy
                dategtCondition.Values[0] = "05/25/2008 22:59";
               

                // Create the filter
                FilterExpression dateFilter = new FilterExpression();
                dateFilter.FilterOperator = LogicalOperator.And;
                dateFilter.Conditions = new ConditionExpression[] {dateCondition,dategtCondition};

               
                // Put everything together in an expression
                QueryExpression qryExpression = new QueryExpression();
                qryExpression.EntityName = EntityName.asyncoperation.ToString();
                qryExpression.Criteria = dateFilter;
                qryExpression.ColumnSet = cols;

                // Return all records
                qryExpression.Distinct = false;
               
                // Create the request.
                RetrieveMultipleRequest retrieve = new RetrieveMultipleRequest();

                // Set the request properties.
                retrieve.Query = qryExpression;

               
                // Execute the query
                Console.WriteLine("Running Retrieve Multiple...");
        BusinessEntityCollection logsResultSet = service.RetrieveMultiple(qryExpression);

                Console.WriteLine(" Retrieve Multiple complete.");


               int number_logs ;
               number_logs = 0;

               Console.WriteLine("Now delete each log...");
                // Iterate through each contact to build a call list
                foreach (asyncoperation aLog in logsResultSet.BusinessEntities)
                {
                    // Access only columns included in the column set of the query expression
                    // NOTE: All other columns will be null EXCEPT for the entities ID, which is always returned

                    number_logs = number_logs + 1;

                    // Console.WriteLine("Asyncoperation ID: " + aLog.asyncoperationid.Value+"Started On: " + aLog.startedon.date);
                    // Console.WriteLine("Asyncoperation Name: " + aLog.name);
                   
                    // Create the target object for the request.
                   
                    TargetDeleteAsyncOperation target = new TargetDeleteAsyncOperation();

                    // EntityId is the GUID of the record being deleted.
                   
                    target.EntityId = aLog.asyncoperationid.Value;

                    // Create the request object.
                   
                    DeleteRequest delete = new DeleteRequest();
                    delete.Target = target;

                    // Execute the request.
                   
                    DeleteResponse deleted = (DeleteResponse)service.Execute(delete);
                    // Console.WriteLine("has been deleted.");
                }

                Console.WriteLine("Number of Logs: " + number_logs);
                Console.ReadLine();

     

            }
        }
    }

     

     

    Saturday, June 7, 2008 11:43 AM
    Moderator
  • Hi there.

    If you want a nice step by step guide on how to clear this, then have a look at this: http://www.interactivewebs.com/blog/index.php/crm/slow-performance-or-large-database-file-in-ms-crm-asyncoperationbase/

    Easy to follow and the code works!
    Wednesday, February 24, 2010 3:24 AM