Page Type field versioning best practices

I should mention they are using this to track ALL changes, including changes to the objects themselves, not just for field tracking.

Virgil, I believe that Continuous Integration could be used here - it will allow you to keep those changes under the source control, which will reduce database size and improve overall performance.

Roman, good thought...they are still in 8, but that could definitely be a discussion point

Hi Virgil,

So what's the actual problem? Are they short on disk space where the 20GB DB is too large or is there just too much history? Kentico's version history is a great way to keep things in check especially if you can't upgrade to v9.

First start with ensuring all the versioning is setup properly in Kentico and will eliminate older version history as needed.

Secondly, I'd check to see if the version history is the actual problem in the database. Run a script to see which tables have the most data in them. In the last few months I've worked with some large websites who had analytics turned on but never managed the analytics data. One database was 153GB in size. After cleaning up the analytics data to the last year or less, I was able to get the database to under 12GB. Another I just worked on last week is at 18GB but they didn't have all tracking turned on. Once I removed the history in Kentico, it cut the database back to 4GB. My guess is you'll find other tables with more data in them than the version history. Some tables to check are CMS_Email, all the Analytics_XXX tables, CMS_EventLog, Staging_Task. I also have a script which gives row counts for all tables, views and indexes.

There are other options like upgrading to v9 and purchasing RedGate but they come with an expense so I'd say use the tools Kentico provides because they work very well but make sure the tools are configured correctly before going with another solution.

Good luck!

Hi Virgil,

I would definitely start by examining which particular tables have the largest size as Brenden suggests. Here are some additional considerations:

• v9 Supports storing binary data of attachment versions in file system, that could greatly reduce the size of your database if the biggest table is attachment history

• another option to consider is setting System -> Log metadata changes, which logs changes to particular fields in event log. The setting doesn't have the best name though, in fact it is logging data, but just changed ones

• if none of that helps, you can always add custom handlers (ObjectEvents.Insert.After, ObjectEvents.Update.Before, ObjectEvents.Delete.Before) and log whatever custom information you need to either custom table, custom module class, file system or whatever storage suits you, BaseInfo has methods which can provide you with the changed data information, an example can be seen in this article: http://devnet.kentico.com/articles/kentico-8-technology-event-handlers

Thanks for all the thoughts. I did actually run a script I have to see the table sizes, hence my question on good practices, the two biggest tables in a couple of their projects (after turning on all object versioning) was the Object Versioning table (averaging between 10-15GB) and EventLog (averaging 2-4GB, but with millions of rows). A lot of the projects we work with are highly transactional and changes are happening constantly on a lot of content, so this method of tracking is creating really large tables.

Where this is not a storage issue (its in Azure) it is causing a lot of challenges when taking backups, making copies, etc for different purposes. That is what prompted me to ask what others do to keep better track of content and page changes vs. just using the object versioning.

Thanks.

You may want to event put a limit on the event log size. Seems like thats a lot of data to keep, unless it is used frequently for troubleshooting. As far as the versioning, how many versions would have to be kept? Im looking at a version 7 site, and there are properties to set the major and minor version length. That could possibly help, only drawback there would be that eventually they would be removed from the system, or minor version promoted to major after the period of time specified in the settings. Just a thought, some clients want all data no matter what, even if they most likely will never use it, but if thats their choice, and they want to pay the upgraded cost for maintaining it, then so be it.

I agree with Josh, limit the event log and no need to log all the details of every object change if you are keeping versions of them. Versioning will do that already, no need to log them too. The event log is pretty much useless if it gets too large, takes a long time to write to it and read from it. I set the event log to between 5000 and 10000 depending on the client.

You might also look at setting the minor versions a bit higher threshold if they are editing a lot too.

Thanks all for the comments, was really curious about what others are doing and this helps.