|
Article relates to
|
Sitefinity 3.x Standard Edition
|
|
Created by
|
Georgi Chokov
|
|
Last modified
|
06/27/2008
|
|
Last modified by
|
Georgi Chokov
|
We had a couple of requests and questions about reducing the Sitefinity project database size. We understand that some hosting providers have database size limitations, therefore we decided to write some guidance on this topic.
Why is our database so large?
The size of the database depends pretty much on the amount of the pages and content items you have. There is an option for Sitefinity to save every modification of a page or generic content item. Given that fact, in the end of your development the history information might be enormous. Imagine you have 300 pages, and each page has been changed 4-5 times during the development stage. The result is you have over 1200 pages as a history, and you need only 300 for your production server.
How do we proceed with all this information? Do we need it at all?
There are basically two things you could do:
- Turn off the versioning for the project, so no backup versions are created
- Purge the history before migrating to the production server
- Store more images in the file system instead of in the database
It’s up to you to decide which choice you will take.
A good practice would be to leave the versioning turned on during the development stage, and purge the versioning information after you are sure that the work is done. This way you will have the latest changes only for your live environment. Another solution could be to prepare a special copy of the database which does not have the history information, and to use that copy for the production server.
How to apply the solutions?
In case you decide to turn off the versioning, you could do it through an option in the web.config file. In the following section
| <cmsEngine defaultProvider="Generic_Content"> |
| <providers> |
| .. |
| </providers> |
.. set the versioning property to false for all desired generic content based modules. For example, disabling the versioning for News would look like this:
| <add name="News" |
| urlRewriteFormat="[Publication_Date]/[Title].aspx" |
| allowWorkflow="True" |
| urlDateTimeFormat="yy-MM-dd" |
| urlWhitespaceChar="_" |
| visible="False" |
| defaultMetaField="Title" |
| securityProviderName="" |
| allowVersioning="False" |
| ApplicationName="/News" |
| versioningProviderName="" |
| commentsModeration="true" |
| connectionStringName="GenericContentConnection" |
| type="Telerik.Cms.Engine.Data.Providers.DefaultProvider, Telerik.Cms.Engine.Data" /> |
| |
Disabling the page history is similar. In the web.config file, set the allowPageHistory property to false:
| <cms defaultProvider="Sitefinity" pageExtension=".aspx" projectName="kb" disabled="false" pageEditorUIMode="Overlay"> |
| <providers> |
| <clear /> |
| <add connectionStringName="DefaultConnection" |
| allowPageHistory="false" |
| allowPageWorkflow="false" |
| name="Sitefinity" |
| type="Telerik.Cms.Data.DefaultProvider, Telerik.Cms.Data" /> |
| </providers> |
It’s important to know that disabling the versioning and the page
history will not delete all existing versions of the content items!
In order to wipe the page history, as well as the generic content item
versions, you have to run a SQL query against the project database.
All
old page and generic items versions will be irreversibly deleted and
only the last published ones will be available. That is why it is very
important that you did a backup of the database.
The query with test database:
| ------------------------------ |
--Initial size : 137 MB
--Deleting old Generic Content items |
| ------------------------------ |
| truncate table sf_VersionItem |
| truncate table sf_VrsBinData |
| truncate table sf_VrsLockedItems |
| truncate table sf_VrsTxtData |
| --Deleting Page History |
| truncate table sf_StagedPageContent |
| |
| update sf_PageContentBase |
| set version = 1 |
| |
| DBCC SHRINKDATABASE (test_db) |
| ------------------------------ |
--Size : 115 MB
--Deleting old and unused (not linked) controls, |
| ------------------------------ |
| delete from sf_StagedCtrlLinks where PageID not in (select ID from sf_PageBase) |
| delete from sf_CmsCtrlLinks where PageID not in (select ID from sf_PageBase) |
| |
| delete from sf_WebControlBase where ID not in (select ControlID from sf_CmsCtrlLinks) |
| delete from sf_CmsWebControl where ID not in (select ID from sf_WebControlBase) |
| DBCC SHRINKDATABASE (test_db) |
| ------------------------------ |
| --Size: 108 MB |
| ------------------------------ |
| --Change log file to 0% if SQL Server permits to, |
| --Left click on your database, Tasks -> Shrink -> Files |
| --Check : Reorganize PAGES before releasing unused space, and |
| --Shrink file to minimum allowed size |
| ------------------------------ |
| --Size 107 MB |
| ------------------------------ |