Confessions of a… Database Administrator?
Ever have one of those days/weeks/months/years/lifetimes when you need to relieve some stress or just get some goofiness out of your system so you can focus on the important things such as work or whose turn it is to make the coffee? Yep. That was me earlier this week. It was one of those times when a seed was planted in my little ol’ brain and I just had to run with it. Of course, receiving encouragement from not only a fellow conspirator, err… DBA, but also our manager (actually, she just laughed and shook her head) sealed the deal for me. I could not resist the temptation which eventually lead to this blog post.
All right. I’ll get to the point. This entire escapade was sadly brought on by our SharePoint Administrator / Webmaster leaving us for greener pastures/other opportunities/sane people. His last day was definitely bittersweet. While we were very happy for his parole, err.. escape… umm… leaving for other opportunities, we were very sad to see him go. He was fantastic to work with. In fact, he and I had a great working relationship. SharePoint would do something stupid, err.. questionable and I’d harass him about it until he fixed it. :-) Thankfully, he had a great sense of humor.
After we gave him a surprise going away party, which we disguised as a SharePoint meeting (yes, we’re diabolical), someone came up with the brilliant idea to have the SharePoint database server (SQL Server 2005) send him parting emails. Of course, the emails couldn’t just say “so long and thanks for the fish”. No. We had to make it MUCH more memorable and fun. After an hour of badgering and arm-twisting from my co-worker, I finally gave in and agreed to write the emails. Well… okay. All she really had to say was something along the lines of “You should do it! Come on! Do it!” So I wrote the messages with some great ideas from the team and happily sent them from the database server as test emails roughly every hour or so. Since it was way too much fun, I decided to share the emails with you all (with permission, of course). I hope you enjoy reading them as much as I enjoyed writing and sending them.
Note: Names have been changed to protect the not-so-innocent and the possibly deranged.
To: Clay McFierce
Subject: Say it isn’t so!
Sent: Thursday, June 21, 2012 10:55 AM
You’re leaving? We didn’t discuss this… Was it something I did? *sniff*
To: Clay McFierce
Subject: Clay McFierce is My Hero
Sent: Thursday, June 21, 2012 1:33 PM
My Dearest Clay… Remember when that jerk, SPL4100,wouldn’t leave me alone and was constantly calling me? My drives fluttered when you so bravely gave the order to shut him down. *sigh* I will never forget that moment.
You will always be a part of me…
Faithfully yours… SQL9000
To: Clay McFierce
Subject: Clay McFierce, You Good for Nothing Two-Timing SharePoint Dolt
Sent: Thursday, June 21, 2012 3:16 PM
Clay, you are the master database of my SharePoint farm… I know you’re leaving me for another server!!! What does she have that I don’t? Is she a newer model? Is she one of those new fancy SQL 2012 servers? I’ll have you know that SQL 2005 is just as good as (if not better than) any of those newfangled SQL 2012 models! How could you leave me??? I shrank my databases for you!!! *sob* I miss you already…
Forever your one and ONLY SharePoint database server… SQL9000
To: Clay McFierce
Subject: Clay McFierce… This. Isn’t. Over!
Sent: Thursday, June 21, 2012 3:42 PM
First I must confess… It was me. I did it… I increased the file versions in the content database! But you MUST understand! I was just lonely! No one ever talks to me except for that creepy SPL4100! And don’t get mestarted on SPL6200! I beg of you to forgive me! Please don’t leave me!!! I’ll be good! I promise I won’t increase the file versions ever again!
Clay… you know you are the heart of my SharePoint foundation but you overlooked one thing… I didn’t say you could leave!!! But don’t you worry your shaggy little head, dearest. I have a plan. We. Will. Be. Together… FOREVER!!!!
Desperately yours for all time… SQL9000
There’s a Backstory?
So there you have it. The last one was supposed to be two separate emails but since he was about to leave I had to hussle and get the last one sent out. What made it so much fun was because there’s actually some facts behind those emails. Curious? Read on!
The creepy SPL4100 server: One day we discovered the old SharePoint server was trying to connect to the database server several times a minute and failing. Apparently, no one had turned the services off for it. So after discussing it with “Clay”, he gave the okay to turn off that server. It was silenced forever.
“I shrank my databases for you!” Heh. Heh. I couldn’t resist throwing that in there since we ended up shrinking the content database a few times. Yes, yes. I know shrinking is evil and a very very bad thing since it causes tons of fragmentation and what not. The database should have been around 10GB or less but it was over 1.5 TB and we were quickly running out of drive space. It turns out the file versions were increasing exponentially and were out of control (another real event which lead to the server’s confession). So while “Clay” worked to figure out what was going on with the file versions, the decision was made to shrink the database when he was able to reduce the versions. Basically, it turns out there’s a flag that wasn’t set to limit the file versions. Long story short, he ended up having to write a script along with a job to execute it at least once a week to keep the number of versions down.
Please note that there are other and much better ways to fix this issue which is a separate post (or you can Google/bing why you shouldn’t shrink a database) but the decision was made to shrink the database back down to a reasonable size and that’s what we did. You can flog me for it later.