0

I need to significantly reduce the size of a Kentico 7 database. Looking at the tables using the query below (source), 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 set the AttachmentBinary column to null in the CMS_AttachmentHistory table.

I don't know that there is a similar possibility in the CMS_VersionHistory table.

Simply truncating the tables doesn't work due to FK constraints, so I'm hesitant to go further along that route.

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

Community
  • 1
  • 1
Charles Wesley
  • 828
  • 12
  • 27

2 Answers2

1

Look to see where you are storing files. You can cut down on the size of the db a lot if you store on them on disk vs in the db. There is a setting under CMS SiteManager > Settings > System > Files : Storage. Check Store files in file system. Uncheck Store files in database.

It's best to do this in the beginning of developing the site. If the site already has everything stored in the db you can move the files to disk, http://devnet.kentico.com/articles/moving-file-storage-from-database-to-file-system

Dukebaby
  • 204
  • 4
  • 13
0

There isn't any similar way for this table as it's related to documents and there aren't any binary data stored.

You can change the length history in Settings as it is described in the Dev Guide: Configuring object versioning

Best regards, Martin

Martin Danko
  • 1
  • 6