It’s Just Poison…

Around 4:45pm, I was able to witness the most beautiful sight! Something that I’ve been wanting to see. Scratch that, something I needed to see for over 2 weeks now.

For over 2 weeks straight. Literally, all day, all night, and sometimes overnight all-nighters, I spent trying to figure out the database issue that has been causing so many problems with BodyMod.org.

For all this time, my DB server would run at 100% CPU utilization, and with throwing up errors that only returned 3 sites to troubleshoot from in Google. I tried everything. I rewrote my code (a whole hell of a lot), converted a lot of my sql statements in code to stored procedures, added more ram to the server, changed pagefile settings, messed with IIS settings, modified database connecting code, added / messed with connection pooling though .NET to my DB server, created new indexes, reindexed the old ones, changed network configurations, changed database security privileges, installed countless patches, defragged the drive, and tried every server hack under the sun to get it to work. I ran the SQL Analyzer, profiler, tuning wizard. Nothing.

My web server would run nicely for about 1.5 - 2.5 hours and then shit the bed with constant ‘Insufficient memory to run that query’ statements to a point where the database services would just seize and die. This of course required me to login and start it manually (the automatic “fail-safes” never kicked in). On top of that, my error logs would have this message 20-30 times per second(!) for an entire day. We’re talking log files in the GB range:

2008-01-17 03:41:48.43 spid    The query notification dialog on conversation handle '{GUID}.' closed due to the following error: '-8470Remote service has been dropped.‘.

I am posting this so that if anyone else has this problem, you’ll know what to do. The fix? Reinstall SQL server. Yep. I backed it all up, wiped it out, and reinstalled it from scratch. Don’t just unmount and then attach the DB’s either. Backup, then Restore.

What did I finally get after all this BS? The most beautiful site…

I guess the good that came of this is that I now have an extremely fine tuned website, I know more about SQL memory management than I would ever need to know, I can trace a SQL server like a champ, and I learned how to create stored procedures. So I guess it’s not all doom and gloom, even though there was a point where I was just going to flip the switch and say “fuck it” (after I’d been up about 48+ hours straight staring at SQL Logs and a green wavey line about a centimeter higher than would have preferred.)

I’m also hoping that it stays this way. I’m just going to have to keep my fingers crossed for the next few days.

Tags: Database, Rants //