A few months back I was working on my blog and did some database maintenance to help slim down the size of things with respect to referral logs based on some logic posted by Phil Haack. Just a couple of days ago, I wanted to see how things were looking in there and noticed the database was starting to get a little big again.

The problem with doing database maintenance, at least for me, is that I don’t have a dedicated SQL instance and I don’t have administrative rights, so I can’t, for example, run a database backup to truncate the transaction logs, and if I mess things up I’m at the mercy of the operator on duty to eventually get to my help desk request and restore me. I also have to have them open up their firewall on a per-IP-address basis so I can connect with SQL Management Studio, and then I’m still sort of stuck because I can only connect from home - the firewall at work blocks that port, so I can’t fix anything on the fly during the day. Normally this isn’t a big issue, and the folks at my host are really good and pretty responsive, but it does limit my abilities.

What this all boils down to is that I need an administrative interface to do this sort of maintenance that’s part of the application. So that’s what I wrote.

Download the zip file, then drop the enclosed ASPX page in your Subtext “Admin” folder. It’s an administration page so you do have to be logged in as an admin to use it. It doesn’t add any navigation links to the admin site, so you do need to manually enter the URL to the page to get to it, but once you do, this page allows you to:

• Clear the error log. Yes, you can do this from the error log page, too, but it’s nice to have all of this in a central location.
• See how many referrals you have in your database vs. how many of those are from search engines. The page lists out what qualifies as a search engine or spam referral so you’ll know what this means. It’s basically just a list of expressions that the page tries to match the URL against - nothing fancy.
• Remove search engine referrals from the referral log. Qualifying spam referrals are also removed.
• Reindex the referrals table and shrink the database. Do that after you clear out the garbage referrals.
• See some size statistics on your database.
• See the SQL script that the page will execute (in the event you’d rather run it yourself or are just curious).

And, of course, since all the code is right in the ASPX markup, you can adjust it as you see fit.

I have only tested this against Subtext 1.9.5b, since that’s the version I’m running on. (I can’t upgrade to Subtext 2.0 yet due to the medium trust problems.) And, of course, standard disclaimers apply: Use at your own risk, YMMV, I’m not responsible for if this truncates every table in your database and kicks your mom, etc.

[Download SubtextDatabaseMaintenance195.zip]