CS Dev Guide: How to reduce database size? - Part 1
As I said before, I start my developer guide series by pointing to some techniques to reduce the size of Community Server database.
Before going to code, you should know that Community Server has some configurations to save files in database and/or local file storage system. Jaxon has talked about it before and I just give short description with samples here because there are many users who don’t know this.
In Community Server all information about your blog post attachments, forum attachments, file gallery items and photos will be saved in cs_PostAttachments table. By default CS saves the content of files both in database and local file system. In many cases this leads to large databases especially for websites that are hosted on shared services. One the other hand large databases can reduce the performance and speed so it’s better to disable this feature and save files only in local file storage system (this depends on you but saving the files in database is not recommended for small websites).
To disable saving in database you should know how Community Server deals with this configuration. Go to your communityserver.config file. There you can find some nodes like <Gallery>, <Weblog>, <Forums> and <FileGalleries>. Here you configure some common behaviours of these applications. For each node there are two attributes named enableFileSystemStorage and enableDataStoreStorage. By default both of these attributes are set by true. This means that all files in your application will be saved both in database and local file system. As names suggest, by setting their value to false you disable that storage system. So to disable saving in database you should set enableDataStoreStorage attribute for all of those node to false. So you will have something similar to this for those nodes:
<!-- Removed Content -->
<Gallery
allowEncodedUnicodeCharsInMetadata="true"
>
<AttachmentSettings
enableFileSystemStorage="true" fileSystemStorageLocation="~/photos/storage"
enableDataStoreStorage="false" enableDirectLinks="false"
extensions = "gif,jpg,jpeg,png,bmp,GIF,JPEG,JPG,PNG,BMP,Gif,Jpg,Jpeg,Png,Bmp" />
<CacheSettings
enableFileSystemStorage="true" fileSystemStorageLocation="~/photos/cache"
enableDataStoreStorage="false" enableDirectLinks="false"
/>
</Gallery>
<!-- Removed Content -->
<Weblog
defaultTheme = "default" enableSkinCache = "true" enableThemes = "true"
aggregatePostSize = "250" createDirectories = "true" enableCommentRedirects = "true"
servicePostCountLimit = "25" aggregatePostCount = "25" individualPostCount = "15">
<AttachmentSettings
enableFileSystemStorage="true" fileSystemStorageLocation="~/blogs/files"
enableDataStoreStorage="false" enableDirectLinks="false"
extensions = "zip,xml,txt,gif,jpg,jpeg,png,doc,xls,mp3,mmv" />
<DefaultPingServices>
<add url="http://rpc.weblogs.com/RPC2" />
<add url="http://ping.blo.gs/" />
<add url="http://rpc.technorati.com/rpc/ping" />
<add url="http://xping.pubsub.com/ping" />
</DefaultPingServices>
</Weblog>
<!-- Removed Content -->
<Forums>
<AttachmentSettings
enableFileSystemStorage="true" fileSystemStorageLocation="~/forums/storage"
enableDataStoreStorage="false" enableDirectLinks="true" />
</Forums>
<!-- Removed Content -->
<FileGalleries>
<AttachmentSettings
enableFileSystemStorage="true" fileSystemStorageLocation="~/files/storage"
enableDataStoreStorage="false" enableDirectLinks="false"
extensions = "all" />
</FileGalleries>
<!-- Removed Content -->
Another ASP.NET configuration which is useful if you have a public website and your users can upload files directly is an option to limit the size of uploaded files. To implement this, go to your Web.config and add or change <httpRuntime /> element in <system.web> and set its maxRequestLenght attribute by whatever you need as follows:
<system.web>
<httpRuntime maxRequestLength="10000" />
</system.web>
The number you provide here is the maximum size of files (in depth this is not only the maximum size of files but in most cases is) in byte so I limited my file uploads to 10000 bytes (about 1MB).
This was an important point for beginners. In next part I will go to database, itself, and will give you some SQL scripts to remove some unnecessary data.
CS Dev Guide: How to reduce database size? - Part 2
In Part 1 I discussed about some tips to disable database storage for files to reduce the database size. In this part I give you some scripts to run on your database and clear some unnecessary data. Before reading this post, don’t forget to back up your database before running these scripts.
First step is to find some information about your database and its tables and the space they’ve used on your server. Scott Allen had a useful post about some of these tips previously and Phil Haack developed those scripts for SubText. I give their scripts here.
First you can find your tables sizes using sp_MSforeachtable stored procedure:
CREATE TABLE TableSizes
(
table_name SYSNAME,
row_count int,
reserved_size varchar(10),
data_size varchar(10),
index_size varchar(10),
unused_size varchar(10)
)
INSERT TableSizes
EXEC sp_MSforeachtable 'sp_spaceused ''?'''
SELECT * FROM TableSizes ORDER BY table_name
DROP TABLE TableSizes
Generally there are some unnecessary data in your tables. One of them is the information about your post referrals. After a short while of your posts, search engines fill your post referrals list and you don’t need them. You can clear cs_Referrals and cs_Urls tables and delete their items before a special date or delete their items based on a phrase.
To delete all of your items before a special date, you use following code (as Scott Allen provided):
BEGIN TRANSACTION
DELETE FROM cs_Referrals WHERE LastDate < '1/1/2006'
DELETE FROM cs_Urls
WHERE UrlID NOT IN
(SELECT UrlID FROM cs_Referrals)
DBCC DBREINDEX (cs_Urls)
DBCC DBREINDEX (cs_Referrals)
DBCC SHRINKDATABASE(<database_name>)
To delete all of your items based on a phrase which deletes all referrals from search engines, you can use following script (inspired from Phil Haack):
DELETE FROM cs_Referrals
WHERE UrlID IN
(
SELECT UrlID
FROM cs_Urls
WHERE Url LIKE 'http://google.%'
OR Url LIKE 'http://%.yahoo.%'
OR Url LIKE 'http://yahoo.%'
OR Url LIKE '%/Search/%'
OR Url LIKE '%/Search?%'
OR Url LIKE 'http://search.%'
OR Url LIKE 'http://bloglines.%'
)
DELETE FROM cs_Urls
WHERE Url LIKE 'http://google.%'
OR Url LIKE 'http://%.yahoo.%'
OR Url LIKE 'http://yahoo.%'
OR Url LIKE '%/Search/%'
OR Url LIKE '%/Search?%'
OR Url LIKE 'http://search.%'
OR Url LIKE 'http://bloglines.%'
You can add any new pattern that you want to this script.
This was the first step. Now I add new scripts to what those gentlemen provided before. I want to clear cs_deleted_posts_archive table. This table simply stores all deleted items in your Community Server application. Once you delete a blog entry, blog article, blog feedback, forum thread, photo comment and … a new row will be added to this table to save some information about this deleted entry (probably you saw Deleted Posts forum before). Probably you don’t want this information after a while (Empty Recycle Bin) so it’s good choice to delete these rows:
DELETE FROM cs_posts_deleted_archive
Also you can delete all rows that deleted after a special date:
DELETE FROM cs_posts_deleted_archive
WHERE DeletedDate < '1/1/2006'
The last point to mention here is about a case which was happened to myself. When I upgraded my application to CS 2.0 Beta 2.0, CS had a major bug in control panel and I couldn’t delete anything from my application except gallery groups. So when I removed my groups, it didn’t remove its galleries and photos from database. As the result when I checked my database after upgrading to CS 2.0 RTM, faced with those rows in my tables. If you’re faced with this issue, you can remvoe them by deleting your rows based on their PostIDs or PublishedDates.
+ You can write your own scripts to reach to this aim based on your data.
Now playing: System Of A Down - B.y.o.b