Database growth

Posted by Community Admin on 04-Aug-2018 15:55

Database growth

All Replies

Posted by Community Admin on 02-Jul-2013 00:00
Hello,
We developed a site for a client using Sitefinity 5.2 a while back. The client has just gotten in touch with us to say that their database seems to be growing at a surprising rate (about 650 megs a week). All of the growth seems to be in one table: dbo.sf_ppvl_tracking_rcrd_df_ppvl_. The site is a pretty small site with a few dozen pages and minor content updates at times. Can anyone tell me how to resolve this?

Greg
Posted by Community Admin on 04-Jul-2013 00:00
Hi,

We have answered you on the support ticket you have opened. I will share this information with the community:

Table sf_pprvl_trckng_rcrd_sf_pprvl_ stores information for each item or page in Sitefintiy and its workflow status whether it is pending approval or it is scheduled, however there was an issue in Sitefinity 5.x versions, the data in this table was not deleted when content or page that entered this data there was deleted.

In Sitefinity 6.0 this problem is fixed, the system properly deletes the approval tracking records in this table after you delete the items or pages that entered them. Our suggestion is to upgrade your project to Sitefinity 6.0. More information about the upgrading process could be found in our Sitefinity documentation.

Regards,
Stefani Tacheva
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 Public Issue Tracking system and vote to affect the priority of the items
Posted by Community Admin on 04-Jul-2013 00:00
@Stefani So does 6 retroactivly go back to clear out all the bad entries, or is it iust *new* items that are properly cleared?
Posted by Community Admin on 04-Jul-2013 00:00
Thanks for the help Stefani and for the script.
Posted by Community Admin on 16-Nov-2013 00:00
Can anyone answer Steve's question please?
Posted by Community Admin on 14-Dec-2013 00:00
We've upgraded from 5.4 to 6.2, and our database is still huge. The sf_pprvl_trckng_rcrd_sf_pprvl_  table is over 4GB in size still!!
How do we remove the unnecessary records?
Posted by Community Admin on 27-Oct-2014 00:00

Is there anyway to fix this without having to upgrade to 7?

Is there a way to clear these tables at least? I see mentioned above about a script. 

Posted by Community Admin on 30-Oct-2014 00:00
Hi all,

The following problem connected to some tables which are not updated after items are deleted or which are not used by the items at all:

feedback.telerik.com/.../99575-workflow-related-tables-not-updating-entries-property-after-workflow-items-are-de

Has been resolved in Sitefinity 7.0 version. A permanent fix has been included in the Sitefintiy 7.0 version, where we drop these tables with the upgrade script below. as they will no longer be needed by our logic.

It is recommended to upgrade your project to Sitefinity 7.x in order to prevent this behavior and take advantage of all available fixes and new feature. More information about the upgrade could be found on the following URL.

However, you could test to execute the script below on a backup of your database and see whether the project  would be working properly.

NOTE: before proceeding please verify if CRUD operations are working fine on all Sitefinity content
1. Ensure you have a full database backup before proceeding
2. Execute the following SQL script against the database in SSMS:


truncate table sf_pprvl_trckng_rcrd_sf_pprvl_
truncate table sf_pprvl_trckng_rcrd_mp_sf_ppr
  
delete from sf_approval_tracking_record_map
delete from sf_approval_tracking_record
  
update sf_news_items set id = NULL
update sf_list_items set id = NULL
update sf_events set id = NULL
update sf_blog_posts set id = NULL
update sf_dynamic_content set id = NULL
update sf_page_node set id2 = NULL
update sf_news_items set id = NULL
update sf_content_items set id = NULL
update sf_media_content set id = NULL
update sf_draft_pages set id2 = NULL

3. Once complete please verify if CRUD operations are working fine on all Sitefinity content

Have in mind that there are some more upgrade scripts that are executed during upgrade and which remove unused records. Our suggestion is to consider an upgrade to Sitefinity 7.x.


Regards,
Stefani Tacheva
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
 
This thread is closed