+1-888-365-2779
Try Now
More in this section

Forums / Bugs & Issues / Approval db table grows out of control

Approval db table grows out of control

4 posts, 0 answered
  1. Jan
    Jan avatar
    39 posts
    Registered:
    22 Jan 2010
    04 Sep 2015
    Link to this post

    I have a sitefinity 6.3 db with eCom. The eCom is populated with products from a backend db. The problem showed when the import function got a hick-up and looped same products over and over again.

    What's happend is that the table sf_pprvl_trckng_rcrd_sf_pprvl_ now got over 80 millions rows and take up over 8 gb of data.

    The db consists of about 60k products, and since it's a master/live versions I guess it grows rater quickly. Another thing is that the product changes quite alot because of an internal item structure.

    What I now need to do is to disable the approval log for products (if possible) and clear the sf_pprvl_trckng_rcrd_sf_pprvl_ table. Also table sf_pprvl_trckng_rcrd_mp_sf_ppr seems to be involved. IS there any way to do this?

    I attach a screen of top size table 

     

     

  2. Jan
    Jan avatar
    39 posts
    Registered:
    22 Jan 2010
    04 Sep 2015
    Link to this post

    Found that product.ApprovalTrackingRecordMap.ApprovalRecords has alot of records. I also see that I can get the current by calling product.GetCurrentApprovalTrackingRecord

    Is it safe to just delete all records but the current? Or do I need to do this another way?

  3. Sabrie Nedzhip
    Sabrie Nedzhip avatar
    534 posts
    Registered:
    26 Sep 2016
    08 Sep 2015
    Link to this post
    Hello Jan,

    Can you please refer to the below forum thread where the same has been discussed:

    http://www.sitefinity.com/developer-network/forums/bugs-issues-/database-growth

    Regards,
    Sabrie Nedzhip
    Telerik
     
    Do you want to have your say in the Sitefinity development roadmap? Do you want to know when a feature you requested is added or when a bug fixed? Explore the Telerik Sitefinity CMS Ideas&Feedback Portal and vote to affect the priority of the items
     
  4. Jan
    Jan avatar
    39 posts
    Registered:
    22 Jan 2010
    10 Sep 2015 in reply to Sabrie Nedzhip
    Link to this post

    Hi and thank you for your answer. The site will function normally but when I do an unpdate to a product and publish it will throw an exception

     

    SitefinityWebApp.Pictura.UpdateProductsScheduledTask Exception in ExecuteTask (No row for Telerik.Sitefinity.Workflow.Model.Tracking.ApprovalTrackingRecordMap ('sf_approval_tracking_record_map') GenericOID@23059861 ApprovalTrackingRecordMap id=d9193158-f43b-682b-ba12-ff0000fa856d NOTRES )

    Stack trace:
       at Telerik.OpenAccess.SPI.Backends.ThrowException(Exception e)
       at OpenAccessRuntime.ExceptionWrapper.Throw()
       at OpenAccessRuntime.DataObjects.PCStateMan.handleException(Exception x)
       at OpenAccessRuntime.DataObjects.PCStateMan.getObjectFieldImp(PersistenceCapable pc, FieldMetaData fmd, Object currentValue)
       at Telerik.Sitefinity.Ecommerce.Catalog.Model.Product.OpenAccessEnhancedGetapprovalTrackingRecordMap(Product owner)
       at Telerik.Sitefinity.WorkflowItemExtensions.CopyApprovalRecords(IWorkflowItem source, IWorkflowItem destination)
       at Telerik.Sitefinity.Lifecycle.LifecycleDecorator.CopyProperties(ILifecycleDataItem source, ILifecycleDataItem destination, CultureInfo culture)
       at Telerik.Sitefinity.Lifecycle.LifecycleDecorator.CheckOut(ILifecycleDataItemGeneric master, CultureInfo culture)
       at Telerik.Sitefinity.Lifecycle.LifecycleDecorator.Telerik.Sitefinity.Lifecycle.ILifecycleDecorator.CheckOut(ILifecycleDataItem item, CultureInfo culture)

    I notice that in the sql you're referring to none of the sf_ec tables are included, maybe their content and the product content link to their images should be included in the update set id part?

     

    EDIT: I tested to include this in the script

    update sf_ec_product set id2 = NULL

    and now the update of products work as well! Still want to know if the is any other tables in eCom I should have included?

4 posts, 0 answered