Did you know that if the data drive fills up on your SQL server it, umm, stops working?  Well, I always presumed that, but had never actually encountered the situation until the other day.

We're extremely anal about monitoring around here.  So much so that we have an entire team of about 50 people devoted to it full time (dev'ing, architect'ing, maintaining, etc.)!  Well, it might not be 50 people, but I'd bet my last dollar that it's at least 30. :)  Anyway, we had our drive space monitoring setup, but had screwed up by not entering a threshold to alert on!  D'oh!

So lo and behold, I get a call on Sunday from one of my teammates that our data drive is filling up (10 MB free on a 52 GB logical drive) and SQL is throwing alerts.  I logged in to take a peek at it and found that SQL had only thrown a few alerts, but was otherwise running just fine.  Our main database file was using 50 GB of the disk space while a few smaller DBs were using the rest.  I told her (my teammate) that we could just leave it until Monday morning. 

Was I f**kin wrong or what!  SQL stopped responding completely at 2:57 AM because the PRIMARY file group was full and it couldn't write any information to the databases.  It had also marked our main database 'suspect' so it was not usable at all.

Well, the long term fix is that we're doubling the hard drive space in that disk array, but the short term fix was pulled straight out of my arse.   In short, I detached one of the smaller databases, moved it's .MDF file to another logical drive to free up space and re-attached the database to the moved .MDF file.  Once that was done I could actually manipulate the suspect database.  I shrank the 52 GB database [exact command = DBCC SHRINKDATABASE (N'db_Name',0,TRUNCATEONLY)] so that there was zero extra space in the file and that freed up 2 GB on the logical drive.  Within about an hour the database had grown back to 52 GB (yes, it is set to auto-grow in 1% increments) so I setup a SQL job to shrink the database every 2 hours. 

It has been running fine for the past 3 days now in my pseudo-fixed state while we're waiting for the additional hard drives to be installed (the processes around here can be extremely painful!).  One thing to keep in mind is that I have a lot of theoretical knowledge about SQL server, but limited practical...I'm far from being a DBA which is why I said that I pulled this fix out of my arse.  This is what it means to be in operations though.  We fix the broke sh*t to keep it running as long as possible.  :) 

One other thing for any of you out there supporting SQL server and I can't emphasize this enough.  Use the Books Online!  Almost everything you need is detailed in this documentation and comes free with your SQL installation!  BOL has saved my hide more times than I count. :)