Database growing fast (sf_stat_sentence) Cancel

1. Gary

   

   28 posts
   Registered:
   16 May 2012
   06 Jan 2014

   Link to this post

   Hi,
   
   My website crashed on Christmas morning (yay), and the cause of it was the database has reached its maximum size on Azure (1GB).
   
   I brought it back up by increasing the size to 5GB, and it's working fine now.
   
   However, this is only a temp fix and not a solution to the problem.
   
   The "sf_stat_sentence" table seems to be the cause of the database growth. It has managed to reach 215896KB on live, compared to 1992KB on the staging site (Huge difference!)
   
   Can you tell me how I can safely remove any unneeded data from this table and how I can prevent this happening again please?
   
   Many thanks,
   
   Gary
2. Desislav Bonchev

   

   25 posts
   Registered:
   19 Jul 2012
   06 Jan 2014

   Link to this post

   Hello,
   
   Thank you for contacting us.
   
   The "sf_stat_sentence" table is used by the Personalization Module. We are already considering implementing a configuration for this module which will allow you to set a maximum duration of the kept stats and other useful options.
   
   Until then you have two possible options:
   
   1. You can disable the Personalization Module, delete all records manually and compact the database. This way you will free the used resources.
   
   2. You can create a SQL Job which can trigger a script that will delete older records and again compact the database. The table itself has a time_stamp field which can be used for this purpose. You can read more on how to create a SQL Job here: SQL Server - Create a Job
   
   I hope this helps. Please let me know if I can assist you any further.
   
   Regards,
   Desislav Bonchev
   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
3. Jeffs

   

   1 posts
   Registered:
   20 Jul 2007
   18 Sep 2015 in reply to Desislav Bonchev

   Link to this post

   Hello,
   
   You know it has already been implemented parameterization solution to this personalization module? Starting on which version?
   
   It can set up so that this table is not populated?

   I plan to avoid use of processing resources to this recording.
   
   Thank you.
   Jefferson Santos
4. Sabrie Nedzhip

   

   534 posts
   Registered:
   17 Aug 2017
   23 Sep 2015

   Link to this post

   Hi Jeffs,
   
   When the Personalization module is disabled from Administration->Modules and Services no statistics are saved in table sf_stat_sentence and the size of this table will no longer grow.
   
   If you are not interested to apply personalization for the pages on the site to free the DB space taken up by sf_stat_sentence please save a backup of the database and delete the data in sf_stat_sentence table.
   
   However, if you are using the personalization criteria and module, you will need the data from that table and Sitefinity will continue saving the statistics in this table.
   

    

   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

    
5. RSC IT

   

   2 posts
   Registered:
   01 May 2015
   26 Feb 2016

   Link to this post

   Hi - how do we safely reduce the size of this table? We have the Personalization module enabled, though we don't actually have any pages personalised yet, and this table is over 4 million rows and 1.5GB of disk space - in three months. This isn't acceptable. What is the data actually used for? Can we just truncate the table, or prune it to a certain age?
6. Sabrie Nedzhip

   

   534 posts
   Registered:
   17 Aug 2017
   01 Mar 2016

   Link to this post

   Hi,
   
   As I noted in my previous post, if you are not using personalization module you can make a backup of the database and then delete the entries from the sf_stat_sentence table.
   
   

   You can also create a job as suggested in the below forum post and delete older records from this table depending on the date value stored in the time_stamp column.: http://www.sitefinity.com/developer-network/forums/bugs-issues-/database-growing-fast-(sf_stat_sentence)#W7LFxBDL8UmiVQ350SUEDQ
   
   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

    
7. RSC IT

   

   2 posts
   Registered:
   01 May 2015
   07 Mar 2016 in reply to Sabrie Nedzhip

   Link to this post

   Hi Sabrie,

   Thanks for this - what counts as "older"? We could delete everything older than a month, 3 months, 6 months? What does this data do?
8. Sabrie Nedzhip

   

   534 posts
   Registered:
   17 Aug 2017
   10 Mar 2016

   Link to this post

   Hello,
   
   The content of sf_stat_sentence table are created to serve the Personalization module because the module tracks each login for users on the site and when the site is in use the user statistics for each user get saved in sf_stat_sentence table populating a large statistic records for each user.
   
   Data is entered into this table for each page on the site when the module is activated even if the Personalization module is not used and there are no pages that are personalized.
   
   If using the Personalization module this table will save the data for each user action based on which personalization criteria is applied.
   
   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