Approval db table grows out of control
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
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?