I use the open source project SubText to power my blog and have been for years. For me, it works great. However there’s one little dirty secret about it: it logs every single inbound URL request for trackbacks & pingbacks… just like most blog software does. All of this gets logged to two tables: subtext_Referrals & subtext_URLs. After a while, these guys can fill up to be quite big… specifically the URLs table.
Unfortunately there’s a TON of crap and spam in this data. Not only do spammers exploit it, but the hits coming from search engines causes it to bloat quite a bit as well. Because I host my site at a shared provider, I’m allocated only a certain amount of SQL storage and if I go over, I have to pay more.
So last week I got my most recent overage bill and decided to deal with it. My database was about 700MB and all it contained was TEXT data! Investigating a bit showed me that my subtext_URLs table had over 1.4M records and that, combined with the indexes on that table took up over 500MB! Add in the subtext_Referrals table and I had a lot of waste. Unfortunately purging it like others have wasn’t working because my transaction log was filling up and kept failing the delete actions.
If you’re like me and could care less about your pingbacks & trackbacks, what you can do is the heavy handed approach: truncate all this data! Here’s the script I use to purge my subtext_Referrals & subtext_URLs tables. After running this I’m down to just around 25MB… giving me PLENTY of space before I get another overage bill from my host!
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N’[dbo].[FK_subtext_Referrals_subtext_URLs]’) AND parent_object_id = OBJECT_ID(N’[dbo].[subtext_Referrals]’))ALTER TABLE [dbo].[subtext_Referrals] DROP CONSTRAINT [FK_subtext_Referrals_subtext_URLs]GO
TRUNCATE TABLE [dbo].[subtext_Referrals]TRUNCATE TABLE [dbo].[subtext_URLs]
ALTER TABLE [dbo].[subtext_Referrals] WITH CHECK ADD CONSTRAINT [FK_subtext_Referrals_subtext_URLs] FOREIGN KEY([UrlID])REFERENCES [dbo].[subtext_URLs] ([UrlID])GO
ALTER TABLE [dbo].[subtext_Referrals] CHECK CONSTRAINT [FK_subtext_Referrals_subtext_URLs]GO
DBCC DBREINDEX ([subtext_Referrals])DBCC DBREINDEX ([subtext_URLs])DBCC SHRINKDATABASE(0)