Andrew Connell

Cleaning up your SubText database

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....

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)

Andrew Connell
Founder & Chief Course Artisan, Voitanos LLC. | Microsoft MVP
Written by Andrew Connell

Andrew Connell is a web developer with a focus on Microsoft Azure & Microsoft 365. He’s received Microsoft’s MVP award every year since 2005 and has helped thousands of developers through the various courses he’s authored & taught. Andrew’s the founder of Voitanos and is dedicated to delivering industry-leading on-demand video training to professional developers. He lives with his wife & two kids in Florida.