SQL Dumbass

Fighting dumbasses, one query at a time…

T-Log, Truncate, and You’re A Dumbass

I have seen the ‘transaction log is full’ message so many times that I am just numb to it. Others do not share the same joy as I do, and thus they may be enamored with the error message when they see it, as if it was a new puppy. Combine this adoration with a tiny bit of SQL knowledge (so tiny it could fit on the head of a pin), and you have a recipe for making my head hurt.

Just to be clear…if you have executed a query that causes the transaction log to fill the entire log drive…the solution to your problem is not for me to truncate and shrink the log. All that does it stop the bleeding. The real solution would also incude that you not run that same query six more times. And it is especially not a solution for you to send emails to my boss asking why I am having trouble maintaining the stability of the server.

Unless, of course, you want me to stab you in the nose repeatedly with the business end of a paper clip.

Migration Issue

Had this nugget of an error come up yesterday during a production migration:

Server: Msg 512, Level 16, State 1, Procedure usp_name, Line 217

Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used
as an expression.

Seriously.

So, I am supposed to believe that this developer actually performed even the most basic of unit testing on their code? OK, let’s assume that their data in development was such that this code could actually work. What about test and/or QA? Oh, you want to keep blaming the data, saying that the differences in data between environments allows for stuff like this to happen?

Fine.

But you’re still a dumbass.

BizTalk was Built by a Dumbass

We were told by some BizTalk experts that one way to tune the system was to use filegroups. However, the nature of BizTalk implementations vary so much that they cannot recommend an optimal filegroup distribution. But we should go ahead and create them anyway.

WTF?

This on top of the fact that they demand to do their own backups but neglect to clean up the files as they go. Seriously, someone should lose their job over this crap. This sounds like “go ahead and start coding and I’ll go get the requirements”.

Oh, but they did tell us some useful stuff. Like to put the data and logs on different drives. Thanks guys, way to step back in time. What’s next, head to a nightclub in parachute pants?

Cannot connect to server

Had a quick and pleasant exchange the other day with a developer. Seems that they had some missing data, so they decided to register the server in SSMS and look into the matter directly. And that led to this exchange:

Developer: I cannot connect to ServerX. Please fix.

Me: Which database on ServerY are you trying to connect to? Also, please show me the error message.

Developer: DatabaseA. I am using SSMS.

Me: Can you please let me know the error you are getting while trying to connect?

Developer: Finally shows the error, and it only took three emails!

Me: The server name is ServerY instead of ServerX.

Developer: Thanks I will try that instead. It is possible that the load has not completed yet.



So…you are looking for missing data…on a server that does not exist…and the load has not finished yet?

Either you need coffee, or you are a dumbass. Your choice.

Gigs, I tell you! Gigs!

I got an email invitation to a Windows Live chat request the other day from a complete stranger, and normally I’d just get out my credit card, but this time they weren’t offering hot chat action or a Viagra sample.  This person said they had a SQL Server project – not a question, but a whole project, and they wanted to chat about it.

I don’t know about you, but when somebody wants to start an instant messenger conversation about a prospective business transaction, I get a bad vibe.  It screams unprofessional.  I gave him the benefit of the doubt and emailed him back.  This blog post sums up the thread, albeit with names changed and humor improved.  Keep in mind that you’re only hearing my side of the story – I’m sure the guy would paint a very different picture.

Me: I’m trying to accept the chat request, but it’s not working.  It might be the firewall where I’m at.  Can you shoot me an email with a summary of what you’re after?

Him: I’d rather chat or talk in person.  When’s a good time to reach you?

Me: Sorry, I’m not in the market right now, so I don’t take calls without at least a rough idea of what’s involved.  Wouldn’t want to waste your time or mine.  Thanks anyway, and have a great weekend!

Him: Large information retrieval database (gigs) in need of performance optimization and a rethink of architecture.

What I Wanted to Say: Gigs?  Like, gigabytes?  Wow.
What I Actually Said: And what’s the salary range?

Him: This is a consulting gig.  I heard you helped out Company X, so I thought you might be interested in this.

What I Wanted To Say: Ah, the standard universally accepted consulting rate, one miiiiiillion dollars?
What I Actually Said: And the rate?

Him: The question is not the rate, the question is value for the money.  I prefer results-oriented arrangements over pay by the hour.  There’s probably a few hours of ramp-up time involved with understanding the situation, research hours to come up with ideas, some hours to try those out, and then some hours to come up with future directions.

What I Wanted to Say: Ah, you want me to sign a fixed-price contract, find your performance bottlenecks, tell you where they are, and then you’ll take those recommendations to RentACoder.com to have some flunkie implement it for $50 without paying me for my time?  No thanks.
What I Actually Said: Thanks for your email, but it doesn’t sound like we’re making progress.  I wish you the best of luck in your search, and have a great weekend.

Him: Why do you keep stressing the rate without inquiring about the challenge?  If you’re so concerned about the rate, name your price and let’s get past it.

What I Wanted To Say: I base my price on the feeling I get from working with people.  I worked with Company X for free because they’re brilliant guys that I was honored to help out, and they summed up their entire problem in less than 140 characters.  You, sir, are causing a simple transaction to drag out interminably, which means this project is going to blow chunks, so I’m going to charge you $250/hour.  I’ve never actually made that much on a contract, but it’s the only way I’m going to be able to stomach working with you.
What I Actually Said: $250/hour.

Him: Okay, so you can take the number of hours you need to deliver and divide that out.  That will be your compensation should you achieve the result.  Work for you?

What I Wanted To Say: The result you wanted was a “rethink of architecture” – there is no way you’re ever going to be done with that as a deliverable.  That’s a scope creep nightmare on a fixed price contract.  Goodbye.
What I Actually Said: No, thanks, how about hourly?

Him: I don’t know you enough to be comfortable paying $250/hour with no end in sight.  Your asking rate is *very* high, so I am asking you to put your money where your mouth is.

What I Wanted To Say: You nailed it when you said “with no end in sight” – that’s how fixed-price contracts work, and that’s what you want.  I’ve played that game before, and the contractor always loses.
What I Actually Said: If that seems high, then we’re probably not a good fit for each other.  You’ll probably get a more appropriate fit from a contractor site like SoloGig.com.  Just FYI, you contacting me saying you’ve heard about the quality of my work from someone else, and then asking me to put my money where my mouth is – that’s a sure way to end a business conversation.

Him: Regretfully, I must agree that I don’t think this will work out.  Maybe it was because I came to you anonymously, but your attitude has been anything but productive.

What I Wanted To Say: Your Hotmail address has your real name in it, your email sig has your real name, and your LinkedIn.com page has your business name on it.  If you think you’re being anonymous, you should be aware that I can’t see the Groucho Marx glasses you’re wearing while you type emails.
What I Actually Said: Agreed – neither of us were getting good vibes.  For what it’s worth, I was happy to help Company X for free.

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!”

Quick, call Microsoft!

For about a week now a member of our team has been helping a customer with some query tuning. We have examined a lot of pieces to the puzzle, but it all boils down to this:



  1. Same query

  2. Two different version of the database

  3. Same server

  4. One query runs in seconds, the other takes minutes
Now, given those four facts, what would you believe to be the cause? Or, more exactly, what would you look to examine next? We emphasized that the data and schemas were different in the two databases, thus the reason for different execution times. The customer followed a different path:

“It is the same query, against the same server, but different results. Clearly there is something wrong with the database and/or the server. How quickly can we get Microsoft involved for a resolution?”

Uh…not quickly. Not quickly at all. I would rather not call them and waste their time until after you verify the data and schema (which they had not). So, we asked for someone to give us a list of objects involved in the queries (we knew them, but we wanted a complete list anyway). We identified one table that had a different rowcount in the two databases. In one database it had 500 rows. In the other, it had 400,000 rows. Guess which query was running faster?

Yeah, I really want to call Microsoft and get them involved as to what the problem is with this query. I wonder what their first question will be. Hmmm. Could it be about data, or schemas?

Stop Me Before I Kill Someone

I just had a person come to me and explain that we have a month end this Friday. Last time we had a month end on a friday, the database dumps were “lost”…


Me: “What do you mean by ‘lost’?”


Them: “I don’t know, but when we needed to do a restore, they weren’t there.”


Me: “Which one of the dumps? (they have four to choose from)”


Them: ‘I don’t know. It could have been the 7pm. Or the 4am.”


Me: “Well, the 4am is overwritten nightly, so that makes sense, and has always been the case. But the 7pm would be unusual.”


Them: “Exactly. So we need you to make sure that nothing weird happens this Friday.”


Me: “Excuse me? How do you expect me to guarantee that nothing weird will happen this Friday? On Halloween?”


Them: “We don’t know.”


Me: “So, you don’t know what happened previously, and you want me to guarantee that whatever may or may not have happened before, will not happen again this Friday. Is that correct?”


Them: “Yes. Let us know when you have it all worked out.”


Me: “You’ll be the first to know.”

Rah Rah Sis Boom SAaaaaaaaa

Chalk this up to the Third-Party Vendors Suck Department:
When are these flies on the potato salad at the IT Picnic going to get it through their Red Bull-addled brains that SA is off limits?  I had to deal with a webex session only for the point of entering the password into a textbox on a form for a combined application/database installation.  The password was not displayed as plain text (good), but the installation failed.  The failure was a blessing to me; we started troubleshooting the failure and determined the password for the SA login was stored in plain text in 3 separate log files and 2 batch files.

Furthermore, the database installation could have been handled separately by supplying a batch file with the associated .sql files it called to the DBA with instructions.  Ultimately, the process did not even require SA rights, but only the ability to create a database and a login.

I give this vendor credit though.  The did ask after the fact what my recommendations are for the install process and agreed that the pwd issue was unacceptable.  (This was of course our Sales Rep on the account though so that may be a case of “Make the Customer Feel Good.”)

So, now I am going through all 90 SQL instances changing the SA password. (A dumbass move on my part for having the same pwd for SA on each SQL instance.)

There is an old Chinese proverb:  “Do not use an ax to remove a fly from your friend’s face.”  Think that applies?

Sadly, this is true

Things had been quiet for a few days, and I was getting used to coming to work, having time to get a cup of coffee, chat with my cubical (in)mates, and work on some much needed items that allow for our team to be proactive with regards to our enterprise. I should have known that the walls would come crashing down, right?

And there it was. A desperate email from a developer. He needed help immediately. Of course, true to form, he did not provide any details about what the issue was, instead he just directs us to the solution.

“Please delete the log for database [dbname]”.

Uh…ok. My first thought was “how?” In most cases, even if you did remove the log, MSSQL would create a new one when it starts up the database (at least I know you can attach a database without a log). At any rate, after I stopped daydreaming about the “how”, I then started to wonder “why”? This was quickly followed by my thinking “what an idiot”. So, I was moving through all the typical stages of emotions that DBA’s usually have. First, wonderment “how many ways is this possible, and what is the best one”? Then, curiosity “tell me more about the thought process that got you here”. Lastly followed by “what an idiot”.

I then imagined what would happen if I actually did delete the log. The conversation would be similar to:

Him: “I am still having problems, but now they seem worse. Did you delete the log like I asked?”

Me: “Yes.”

Since that scenario was not about to win me any customer service awards, I decided to tell the developer that deleting a log was not advisable. Turns out that did not win me any awards either.

Him: “What do you mean, we have the logs deleted all the time.”

Me: “Really? Do you really mean deleted, or are you asking for the log to be truncated?”

Him: “What’s the difference?”

At this point I am considering calling Microsoft Learning and urging them to revoke the certifications that this developer has “earned”. At the very least an investigation should be opened. To think that this certified developer does not understand the difference only makes me wonder what other things do they not understand.

Dumbass.