About a year and a half ago I updated my Subtext database maintenance page from the original I had posted about three years ago. While the database hasn’t changed much between Subtext and (the maintenance page does still work), I updated the page to provide a little more info and do the cleanup in a more automated fashion.

As a refresher, this page lets you…

  • Clear the error log.
  • See how many referrals you have in your database.
  • Remove search engine and spam referrals from the database.
  • Reindex the referrals table.
  • Shrink the database.
  • See additional stats like table and overall database size.

This new version adds some [super rudimentary] AJAX goodness so you can click the button to remove the junk referrals and it will iterate over the set in blocks of 1000 until they’re cleaned up - so the database connection doesn’t time out.

(The whole reason I have this page is that I don’t really have SQL Server Management Studio access; I have to maintain my stuff entirely over the web.)

Anyway, drop the two enclosed pages into your /aspx/Admin folder and you’ll be able to access them after logging into the Admin console by visiting http://yourblog/admin/DatabaseMaintenance.aspx. It won’t add any navigation links to the site into the page, so you’ll have to manually enter the URL to get there.

UPDATE 2/20/2012 Based on some logic from Tim Heuer I added the ability to enable or disable referrals to the bottom of the maintenance page. Click the appropriate button and the referral stored proc will be modified to track referrals (or not).

[Download SubtextDatabaseMaintenance2520.zip]

I’m hoping that Subtext enables the ability to either stop tracking referrals altogether or enables some sort of spam filter on referrals so these never make it into the logs to begin with.I shrank my database from 245MB to 23MB just by running this maintenance.

personal comments edit

I associate Radio Shack with the DIY crowd

  • folks who probably know more than average about electronic componentry, or at least enough to be dangerous, and need someplace to get raw materials.

Now, people can talk about how there’s retail competition and all that noise, but I think differently. Let me lay the scene:

I just put up some new surround speakers in my game room. The receiver I have in there doesn’t have the fancy Audyssey sound balance microphone thingamajig on it so I have to balance the sound the old-school way: either by ear or with a meter. Where do you get a sound meter? Radio Shack.

I went into the store and the floor space of the place was only about 1000 square feet. The front half of the store was all cell phones and RC cars and so forth. The back half consisted of three aisles, each of which had the gadgets and doo-dads I normally associate with Radio Shack. Being the only customer in the store, I walked up to the desk and asked the lady there, “Where would I find a sound meter?”

“…Sound meter?”

“Yes, a sound meter… SPL meter… sound pressure level meter… you know, something that measures the volume of sound. A sound meter.”

She stood there for a second with a blank look on her face, then looked around the shop. “Um…” She came out from behind the counter and started walking down each aisle, in turn, gazing around without actually looking at anything on the shelves. Then she called to the back for help.

A guy came out of the back and we went through the whole “what-is-a-sound-meter” thing again. After that he, too, went down each aisle, but he went much faster, emulating “purpose.” After visiting each of the three aisles a couple of times, we finally ended in the middle aisle where, lo and behold, they had not one but two models available

  • a digital ($50) and an analog ($15).

“What’s the difference between these two models?” I asked.

Two blank faces. “Um…”

“You know what? $15. Done.” I bought the analog meter (which, by the way, worked perfectly for my purposes) and that was that.

Thing is, if I was looking for something more technical than a sound meter, what would have happened? I mean, I don’t expect much from people, but you’ve only got 1000 square feet of product, and only half of that is actually anything of substance. You don’t know where stuff is or even what you have?

That, my friends, is why Radio Shack is failing.

In iTunes, if you have a track that is missing artwork you have the ability to right-click the track and opt to automatically download artwork for it. This works well if you play the track in iTunes or an iTunes-connected device (e.g., iPod)… but if you also use the same library in a UPnP server to stream your music on your network (like Asset UPnP) then you’ll notice the artwork doesn’t show up. That’s because iTunes stores the downloaded artwork in a separate database outside the actual physical music track file, but other servers/devices expect artwork to be embedded in the track.

Luckily, with a little scripting, you can fix this.

I wrote this script to run on a Windows machine and copy the downloaded artwork directly into the track.

WARNING: THIS SCRIPT MODIFIES THE TRACKS IN YOUR LIBRARY. BACK YOUR FILES UP BEFORE RUNNING IT. That seems obvious, but just in case it wasn’t clear, there you go.

I’ve run this pretty extensively in a test environment and I’ll be running it on my 15K track library shortly. Again, though, BACK UP YOUR LIBRARY BEFORE RUNNING THIS SCRIPT and USE AT YOUR OWN RISK. If your tracks all end up corrupted, you’re on your own.


I’ve been not-so-subtly influencing my six-month-old daughter in a geek-oriented direction. I already mentioned the Bigtrak Jr. I have waiting for her, but I’ve also sprung for some clothing items of choice:

Made With Love (and Science)


(I only bought the Wonder Woman superhero onesie, but the others may be appropriate at some point.)

She’s not quite ready for blocks, but soon I’ll probably look at one of these two sets - Mad Scientist blocks or Periodic Table blocks. I’m not sure which.

Young Mad Scientist Alphabet

Periodic Table Building

Fun stuff.

I was also thinking last night that it’s really too bad that they don’t have Fisher Price video game controllers the way they have toy cell phones. My daughter always wants to grab the controller while we’re playing and it’d be nice to just give her a controller of her own.

media comments edit

After doing that Hanselminutes on network attached storage, Synology, and Windows Home Server, I figured I’d also talk about how we store our original media, in case folks were wondering.

As of this writing, we have somewhere close to 1000 individual DVD discs in various forms - single or multi-disc movies, multi-disc TV sets, etc. - and about as many CDs. Not all of the DVDs are on my Synology DS1010+ - I didn’t rip the “special features” discs, and in some cases where I have multiple editions of the same movie, I only ripped the one we like the most. That puts 890 DVD images in VIDEO_TS format on the DS1010+. All of the music is on the Windows Home Server in iTunes, but not in a consistent format - some is 256k MP3, some is 320k AAC, some is Apple Lossless. Lately I prefer Apple Lossless since it doubles as a backup copy of the music, but I haven’t gone through and re-ripped everything.

With all that media, how do we store it?

Basically, we gave up on keeping everything in the original cases because it’s just too much volume.

The CDs are in threeOdyssey CD storage cases, each of which holds 400 discs in thin “DiscKeeper” sleeves. I have them in alphabetical order by artist, except when it’s a compilation or soundtrack album, in which case it’s in there by title. I made small “dividers” by running some lettered file folders through a paper cutter.

Odyssey CD storage cases - click to

The DiscKeeper sleeves are extremely thin, which is good, because each box is packed pretty tight.

DiscKeeper sleeve with a CD in it - click to

For the movie DVDs, I’m using MSDN binders, but they’re basically like standard Case Logic CD binders that have individual CD-holding pages. I actually use the Case Logic pages in the binders, I just happen to have the MSDN binders available to me.

MSDN binders hold DVD movies - click to

Each page holds two movies, and each binder is pretty full.

DVDs in the MSDN binders - click to

I keep all of the original inserts to the movie DVDs in a box, and the cover art is in an expandable envelope, alphabetized by movie title. If the movie came in a special keep case or printed box, that’s stored in a giant tub in the attic; if it was just one of the standard plastic clamshell cases, it gets recycled.

The binders are just for movie DVDs. TV DVD sets are on a set of bookshelves, alphabetized by title.

TV DVDs on bookshelves - click to

We considered putting the TV DVDs in binders, too, but we liked being able to look at the discs like a library. Honestly, if I had a ton more space, I’d like to have all the discs out like a library so you could browse them, but we don’t have that sort of space.

We have very few Blu-ray titles right now, but those are on a different bookshelf. If we get too many more, I may switch them to binders as well.

Anyway, with all these discs, you can see how picking through binders to grab discs to watch or whatever would be sort of a pain, and if you wanted to browse for a movie, it’d be a similar pain. That’s why I ended up with my media center - so we could, basically, have our own “internal Netflix” with all the movies we own, on demand right there.

To keep track of our inventory (and to have a list for insurance purposes), I use DVD Profiler to track my video discs and Collectorz.com Music Collector to track audio discs. I’ll save details/evaluation on those packages for a different post, but if you’re looking for catalog programs, I recommend both of those.

UPDATE: For my Blu-ray discs, I’m using DiscSox HiDef Pro sleeves.