SQL Dumbass

Fighting dumbasses, one query at a time…

Backups and Boat Drinks

My cell phone rang, and when I looked at the display, it showed a picture of a boat.  I put on my happiest voice and answered, “Buddy!  Pal!  Haven’t heard from you in a couple of weeks.”

Him: “Man, I really need your help.  I know I’ve called you a lot, but this time it’s serious, man.”

“Let your old friend handle it for you.  What’s up?”

Him: “My backups this time.  They keep failing, man!  If I can’t get this database backed up, they’re going to start asking questions, and they’re going to figure out that I – “

“No problem.  Your network guys still haven’t shut down LogMeIn.com, right?  I’ll take control of your desktop.  Just don’t touch the mouse.”

Him: “I swear, man, you get this fixed, and I’ll give you that damn boat!  You can just have it!”

See, there’s a problem.  The great thing about a slow friend with a boat is that he lends it to you all the time and never notices that the gas tank is empty when you bring it back.  I’d rather he kept the boat payments, insurance payments, gas payments, dock payments, and headaches.  I handled it the way a good DBA handles any bad news – he makes lemonade.

“No, I could never do that to you, pal.  Alright, so let’s look at your SQL Server Agent jobs.  It looks like this one here is the full backup job, right?”

Him: “Yeah.  I made my own maintenance plan.  Pretty cool, huh?”

“Nice work.  Why is the start time all over the place?  6pm, 7pm, 5pm….”

Him: “When it started failing, I moved the start time earlier and later trying to see if that was the problem.  But it just keeps failing at different times!”

“Hmmm – no, it’s failing at the same time.  It’s failing around 8pm.  The job duration is just all over the place because you keep moving the start time around.  See, when it starts at 6pm, it fails after two hours.  When it starts at 7pm, it fails after one hour.  When it starts at – “

Him: “You’re a genius, man!”

“No, but I play one on my blog.  So what happens on your server at 8pm?”  After a few seconds of silence, I thought I’d lost him.  “You still there?”

Him: “Oh, me?  You want me to answer that?  I thought it was some kind of trick question, man.”

Ugh.  I went into his agent jobs, sorted by start time, and didn’t see anything suspicious.  He kept swearing he didn’t have any scheduled jobs at night, but I knew better than to listen, so I went into his Windows Scheduled Tasks.  “What’s this one?” I asked, my mouse pointer circling emphatically around a job with a cryptic name.

Him: “Oh.  Yeah.  But that’s good!  I need that.”

“What’s it do?”

Him: “We bought a virtual tape vault thingy that grabs a snapshot of each machine.  I got it in case my backups fail.  And thank God I did, because my backups are failing and now I’ve got a safety net?”

“When’d you get it?”  My mouse zipped through menus until it got back to the SQL Server Agent job history.  “Round about a month ago?  Say, the seventh?”

Him: “Uhhh – yeah, that sounds about right.”

The same time the jobs started failing, of course.  I went back to the Scheduled Tasks, looked at the batch file that was running at 9pm, and saw that the first line was stopping the SQL Server service.  This backup solution was stopping SQL so that the MDF/LDF/NDF files wouldn’t be open, then it ran a backup, and then it started the service again.  Pretty tough to back up the databases through SQL Server while the service is stopped.

The toughest part of DBA work isn’t the technical side: the toughest part is explaining the answer to your customer.  Anybody can say, “Look, you moron, YOU are the problem.”  It takes an artist to say it in a way that causes them to make you a spare set of their boat keys.  I thought fast.

“Okay, here’s what we’ll do.  How big is that virtual tape library?  Is it like a 1u or 2u server?”

Him: “Yeah, it’s just a pizza box with big SATA drives that – “

“Good.  And it only backs up your servers, right?”

Him: “Yeah, I got it out of my own budget.  I had some left over that I had to spend before the end of the – “

“Good.  Unplug it, take it out of the server room and leave it for me in the boat cabin.”

Him: “What?  Man, dude, are you crazy?”

“Relax.  I’m going to plug it in in my datacenter and back up your servers from there.  That’ll give you some offsite redundancy.”  I was lying through my teeth, but one good backup for him was better than two failing backups.  Besides, I could use the virtual tape library in my lab to back up my virtual machines.

Him: “Wow, thanks man!  That’s so cool of you!”

“Anything for you, buddy.  Now I’m going to disable this task, and I’m going to….defragment your heaps.  That’s probably why your backups are failing.”

Him: “What?  What does that mean?”

“Don’t worry about it, I’ll do it for you.  In the meantime you go ahead and run into the datacenter to unplug that virtual tape library, and we’ll be done in a jiffy.”

Him: “You’re the best, man.  I’ll leave it in the galley.  You take the boat all weekend, man, all you want.  I’ll stock the fridge with beer for you!  You always save the day, man.  Alright, I’m going into the datacenter.  Later!”