Approval db table grows out of control - Bugs & Issues - Bugs & Issues - Progress Community
 Bugs & Issues

Approval db table grows out of control

  • Approval db table grows out of control
  • a0d7511e-37c1-6deb-a958-ff0000446526_db-tables.png

    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 

     

     

  • 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?

  • 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
     
  • 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?