And then Elaine says, "It shrinks???"
There's been good information provided by Keyvan Nayyeri and others on how to reduce the size of the Community Server SQL Database. Since I was seeing the size of my dbvtcsdb database grow each time I backed it up in my WebHost4Life Control Panel (which is pretty often) and saw it hit 264 megabytes, I wanted to take action. Why was my little ol' dbvt.com site database so darn big?
I've been storing my photos on the file system and not in the database, so that wasn't why my database was all poofy. I thought maybe trackbacks or the event logs were the primary reason for the bloat, but I wanted to do a comprehensive cleaning and find out what was going on.
Gathering up the best sql scripts from the brightest (mostly Keyvan), I launched the great DBVTCSDB Purge. Here's the script I ended up using. I think I covered everything. I scanned my referrals table and added a lot of spammer urls to the script, truncated statistics, exceptions and eventlogs, cleared out cs_postattachments binary content for my photo gallery just to be sure, rebuilt my search indexes, dumped the transaction log, shrank the database and called it mashed potatoes. Then I backed up my database. 51 MEGABYTES! WOO-HOO!
That was more like it! But this isn't where the story ends. Remember, Elaine concludes, "I don't know how you live with those things!" Indeed, the guys who say "I shrank my database from 1.5 Gigabytes to 100 Megabytes!" always stop here. They have to, because here is where CS Jobs will kick in and the search indexes get rebuilt. The cs_searchbarrel continues to fill, every 15 minutes, with words, words and more words. I sat and watched that turd crank, and by the time the search index tables were rebuilt my dbvtcsdb database settled in at 141 Megabytes.
The moral to the story is that most of my precious dbvtcsdb database ended up consisting of cs_Searchbarrel, and your database probably does, too. I reduced the size of my database from 264MBs to 141MBs, and while it's more than 51 megabytes, that's okay. Community Server search rocks. I couldn't live without it and it's worth 100 megs of my database.
"Like a frightened turtle, Elaine. Like a frightened turtle."