Where databases mysteriously appear and disappear…
First things first, though. My week in review… The user group meeting this last week was definitely worth attending! Kevin Cox’s presentation was seriously one of the best! It was very entertaining and educational all at the same time! The statistics about the largest databases and so on was really cool. I honestly can’t imagine a 1 TB database let alone a 70 TB database. He also went over how they accomplished some of the disaster recovery and high availability achievements for these huge projects. I was impressed. If you ever get a chance to attend one of Kevin’s sessions, I would highly recommend it! He’s now on my list of “must see” presenters. If you’re interested in the slides from the presentation, I put them up on our group’s web site this morning. You can get to them here.
Other than the user group meeting, this week was a bit of a blur. I didn’t get a lot of sleep this last week. However, I did manage to drag my sleepy butt into work every day this week and I managed to get some work done! I think that’s a good accomplishment right there. 🙂 Granted, I ended up inhaling… err, drinking quite a bit of coffee which probably didn’t help me sleep the following nights… bah! minor detail…
I was finally able to install SQL Server 2008, Integration Services and Reporting Services on the new server. Yay! I also configured it and set up the maintenance plans. It was fun. 🙂 It’s good to get away from writing database scripts and fixing bugs in stored procedures once in a while. Yes, I’ll admit it. I’m part developer and part database administrator… you got a problem with that? No? Good! 😉
This week my DBA buddy worked on her very first SSIS package! Woo hoo!!! It was very exciting! Yes, I lead a very exciting life! Can’t you tell? 🙂
Anyway, this week’s post is about checking your drive space, transaction log sizes, and the number of databases on your servers. For this part, I like to keep a spreadsheet to compare with week to week or even daily. You could actually store all this in a database table and do reports on it. That was my original plan but I haven’t gotten around to it. Actually, at my current place of employment, I believe we have jobs that capture similar items on a regular basis. The code I’ve been using is pretty simple (see below) and they are pretty much self explanatory… which his good because I’m still tired but didn’t want to skip writing the blog post this week. 🙂
----Check drive space----
select 'Retrieve drive space info...'
----Check transaction log size----
select 'transaction log size'
DBCC SQLPERF ( LOGSPACE )
----Check number of databases----
select 'Number of databases...'
select count(1) as NumberOfDBs
I like to check the number of databases on the servers because at my last job on occasion a database would either mysteriously appear or disappear… or both. Yes, this is where the “Bermuda Cluster” comes into focus and it happened to me at my last job. Once (thankfully it was only once), a server administrator didn’t realize that removing one of their databases off the SQL cluster could be a big deal… until our disaster recovery process failed because we didn’t know he had deleted the one database the day before the DR test. The process itself is a bit of a long story for another day. Needless to say, once we figured out what happened and tracked down who did it, he was very apologetic. He honestly didn’t realize removing a database could break a few things. It can? Yes, it can! Really? Seriously? Once we all calmly talked about it, everything was fine and it didn’t happen again. This is why I started keeping track of the number of databases on a server and usually their names… and thus started the road to my paranoia and knowing my luck, if I didn’t watch the servers, it’d probably happen again… and again…
As for new databases mysteriously showing up, imagine my surprise when one day I saw 10 new databases on the production SQL cluster which neither me nor my fellow DBA knew anything about. Did aliens just plop them there? I did some asking around and found out one of the managers had asked the former SQL Server DBA (who had accepted a different job position in the company) to put them on the cluster. I wasn’t thrilled but understood. I just asked if they could please let us know when they do things like this so I don’t end up freaking out and deleting any of them… or just freaking out… funny that was before the SQL injection attacks too… another fun story for another day… yes I’m just full of them… hence my ultra-paranoid personality… 😉
So as you can probably now see, this is where I started becoming a bit paranoid. I didn’t really like it when databases would either appear or disappear without us knowing but instead of freaking out, I would calmly ask around and figure out who did what and why. Then proceeded to explain why we (the SQL Server DBAs) should be consulted because the cluster at that time was nearing its capacity. The ten additional databases turned out to cause quite a bit of added complexity due to the nature of the application. That’s another long story… and probably why I have a sign that says “bang head here”. At least it made for interesting… and sometimes challenging… days at work. I really can’t complain, though. It forced me to learn the right or best way things should be done and at least work wasn’t dull or boring. 🙂
On to next week. More than likely, next week’s blog post will be all about our mini-adventures in Miami prior to our departure on the SQL Cruise! Yay!!! One more week!!!! Actually, the next couple of posts will probably be all about the cruise too… so I’ll have to resume my little scripting scheme… err, posts sometime after I get the cruise stuff out of my system… unless they leave me on some island somewhere in the Caribbean… 😉
Updated: To view the prior post in this series, please refer to “Failure? What Failure?” where I explore… umm… failures. Next up is “Peek-a-Who? Ha! I See You!” for those who want to see who has been lurking around in the databases.