Shrinking your Subtext database

I run my site on Subtext which has been around for 6+ years in some form (Subtext is a fork of .Text from way back).  As a part of the framework, there was initially built-in capabilities for tracking referral traffic.  On each view of the application, it would tick a referral note and you could see this in the statistics view of the admin pages.

As the standards (for lack of a better term) of tracking Page Views, Referrals, etc. moved to more proven/consistent reporting like Google Analytics (or other platforms) these type of platform tracking became worthless to me.  I never checked them because, frankly, I didn’t believe them anyway.  The problem is that Subtext is still tracking this information for me and taking up valuable little bytes in my database.

For Subtext, specifically, contributors have created scripts and maintenance pages to help manage some of these referrals that may not matter to folks and are just taking up space.  I am one of those people.  In my recent migration to SQL Azure I wanted to take advantage of 100MB pricing.  Surely my blog was not bigger than that.  To my surprise my blog was 650MB in size. 

What!?

I hadn’t run my database maintenance script in a while and decided to run that which purges the referral tracking.  It got down to 35MB.  Yeah baby.  In fact this topic has been discussed on a few times on the Subtext developer mailing list and even tracking as a issue for the project.  In the meantime I wanted to solve it myself for my blog.

In Subtext there is a stored proc that runs to get some of the entry tracking data called subtext_TrackEntry.  Within that proc is where it looks to see if it is a referral and adds that data.  I simply altered my proc on my end to be like this (keeping in the old function just so that I know what I did in case I needed to revert back):

   1: ALTER PROCEDURE [dbo].[subtext_TrackEntry]

   2: @EntryID INT, @BlogId INT, @Url NVARCHAR (255)=NULL, @IsWeb BIT

   3: WITH EXECUTE AS CALLER

   4: AS

   5: -- Removing the referral tracking

   6: -- if(@Url is not NULL AND @IsWeb = 1)

   7: -- BEGIN

   8: --    EXEC [dbo].[subtext_InsertReferral] @EntryID, @BlogId, @Url

   9: -- END

  10: EXEC [dbo].[subtext_InsertEntryViewCount] @EntryID, @BlogId, @IsWeb

Now I’m no longer tracking referrals because my analytics package is doing that for me already.  My database is now representative of things that matter to me, rather than things I just want to clean up.  If you are a Subtext user and never knew that referral logging was wasting your database (and you are using an analytic package to track that anyway), then I hope this helps!