Is it possible to purge version history for an entire site?

Charles Wesley asked on July 11, 2014 15:10

I need to significantly reduce the size of a Kentico 7 database. Looking at the tables using the query below, I can see that some of the largest tables in terms of data size are CMS_AttachmentHistory and CMS_VersionHistory:

CREATE TABLE #tmpTableSizes
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)
insert #tmpTableSizes
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

select  * from #tmpTableSizes
order by cast(LEFT(reservedSize, LEN(reservedSize) - 4) as int)  desc

I found a forum post from 2013 that implies it is ok to simply truncate the CMS_AttachmentHistory table.

Is it also ok to perform this action on the CMS_VersionHistory table?

Is there a better way to do this other than a SQL TRUNCATE statement?

versioning delete

Correct Answer

Martin Danko answered on July 15, 2014 18:00

Hello Charles,

The most space is taken by a binary data of old attachments. You should be able just to clear that data by SQL command...

e.g.: UPDATE CMS_AttachmentHistory SET AttachmentBinary = NULL WHERE ... your condition to fir your needs (e.g. AttachmentLastModified < DATEADD(month, -1, GetDate()) ... for all attachments older than 1 month)

And the version history length is applied just to a new attachments, not for those which are already stored in the database. But it's good to set the limit lower to protect database size increasing in the future in this case.

If you now change the storage option from database to file system, Kentico CMS will check if the attachment exists in the file system firstly when trying to access the file. If it will not find the attachment on the file system it will try to look for this file in the database. In your case it will find the file and make copy of this file to your file system. This is done automatically after the attachment is accessed.

So, would it be possible to change the setting to file system and?

Files will be created on your file system automatically when accessing them......

If you would like to perform this operation programmatically, you can get AttachmentInfo object from each attachment and use following method:

CMS.FileManager.AttachmentManager..SetAttachmentInfo(AttachmentInfo)

This method will check the current storage setting and will create a copy of the file on file system from database data in case the storage option is set to file system.

So You will need to uncheck the option store files in a database. Only the option Store files in file system should be checked. Now you will need to leave the site as it is for some time. If the image is opened/updated, it will be automatically moved from database to file system.

Best regards, Martin

0 votesVote for this answer Unmark Correct answer

Recent Answers

Brenden Kehren answered on July 11, 2014 16:51

For version history, simply change your settings. It will automatically clean up your version history based on what you set.

0 votesVote for this answer Mark as a Correct answer

Charles Wesley answered on July 11, 2014 17:03

Changing the settings doesn't seem to have retroactively applied to existing content. I took a look through scheduled tasks and I didn't see one that looks like it would do that?

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on July 11, 2014 21:16

You might email support and see if it will clean up as I'd expect it to.

On another note, I'd suggest NOT truncating your version history as it defeats the purpose of having version history unless of course you don't care if you have it.

0 votesVote for this answer Mark as a Correct answer

Charles Wesley answered on July 15, 2014 20:40

Martin,

Thanks for the reply--I have seen the approach you listed above in another thread, and I applied it to my database. However, the CMS_VersionHistory table in this instance is nearly 2GB.

For context, I am setting up development environments on a virtual machines that for operational reasons have limited disk space resources. For business reasons, we allow our users to have many versions of documents in production. In development, we do not need any of this version history.

If I were able to purge version history for the entire site, this would add up to significant disk space savings when you look at 4 development instances (approx 8GB combined for CMS_VersionHistory alone).

As I did change the settings per Brendan's first answer, and Martin's answer suggests that accessing the content is how a migration from DB to FS storage works for attachments, does each piece of content need to be access for the new setting (no version history) to be applied?

0 votesVote for this answer Mark as a Correct answer