Approval db table grows out of control

Posted by Community Admin on 04-Aug-2018 06:59

Approval db table grows out of control

All Replies

Posted by Community Admin on 04-Sep-2015 00:00

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 

 

 

Posted by Community Admin on 04-Sep-2015 00:00

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?

Posted by Community Admin on 08-Sep-2015 00:00

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
 

Posted by Community Admin on 10-Sep-2015 00:00

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?

This thread is closed