×

First time here?

You are looking at the most recent posts. You may also want to check out older archives. Please leave a comment, ask a question and consider subscribing to the latest posts via RSS or email. Thank you for visiting!

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! 


This work is licensed under a Creative Commons Attribution By license.


1/23/2013 12:34 PM | # re: Shrinking your Subtext database
I don’t have access to the log files nor am I able to run DBCC commands anymore, but I’m trusting that SQL Azure is more efficient than my own DBA skills of old and that my database will be managed effectively with regard to these items.

 
Please add 5 and 7 and type the answer here:

DISCLAIMER:

The opinions/content expressed on this blog are provided "ASIS" with no warranties and are my own personal opinions/content (unless otherwise noted) and do not represent my employer's view in any way.