Instead of working, I blog.

Being a DBA is certainly interesting to say the least. And I can honestly say that one of the things I like the most about it is all the diversified topics we get to cover... esp in data modeling. I've modeled DBs for marketing, scanning, healthcare, killing chickens, restaurant inventory, political contributions, shipping drugs/medical supplies, and I'm currently working on a tax return DB.

You can't help but really pick up some interesting information on these projects. For example, not only can ckickens live a long time after you cut off their heads, large processing operations can kill over 600,000 chickens a day. Just think about how many chickens that is, and since they're only about 6 weeks old at the time, they're easy to replace quickly. I have also learned that there's an acceptable amount of rat feces in canned chili, and the rate at which hospitals give you infections due to mishandling and then charge you for the treatment is alarming.

However, probably my most bizarre encounter with a schema to date happened a few years ago. I only mention it now because I happened across my notes and thought it'd make a cool post. A friend who was in vet school at the time asked me to write him a small DB to keep track of phychological disturbances in animals. At first I was prepared to turn him down because I just didn't have the patience for that kind of nonsense, but my curiousity got the better of me.

Among the things I learned during that project were:
--Why cats suck or chew on wool.
--Why dogs chase their tails.
--Why calves cross-suckle (that's when they suck on each other's different parts instead of their mother's milky part).
--Why horses shake their heads.

I'm really curious... what are some of the more interesting projects you guys have come across in your modeling travels? And what are some of the more interesting things you've picked up along the way?

About a month ago, we finished our last round of audits and I wanted to share a little bit with you about how to talk to auditors, or better yet, how not to talk to them.

Here's a fine example of how you should not talk to an auditor.
One of our guys, I'm sorry to say a DBA, walked into a room where the bosses were holding a meeting and announced that none of our backups across the board were working, and had been failing for a couple days. We're completely unprotected!!

Of course, you guessed it, they were meeting with the auditor from D&T. In his defense, he said he had no idea that was an auditor and he never would have said that if he did. OK guys, let's put this rule on the table right now. Don't go announcing things like that at all. If there's someone in the room you don't recognize, keep your mouth shut, send an email, pull them out of the room, whatever, but don't just announce that you're shop is falling apart.

Most companies will tell you when the auditors are going to be there, and will tell you to refrain from discussing sensitive business outside of your immediate area. This is an excellent tactic, and we do that too, so why this incident happened, I'll never know.

So how should you talk to an auditor then? There are 2 areas you need to worry about.
The first is before and after the interview. Auditors like to come up to your desk or pin you in the hall and ask you questions about your environment. That's fine for them, but you need to get with your managers and decide how you're going to handle this situation... remember, anything you say can and will be used against you in a court of audit. What we do is we refer all questions back to our boss. If an auditor asks me a question outside of the interview, I say, send your question to my boss. He then asks me the question, and I in turn send it back to him. This way, the auditor can't trip you up on the spot, and you won't accidentally say something you'll regret. And it gets to go through the filter of someone else. Even if you know the answer, Don't say anything. Make them go through channels. Now you may not choose to do it this way in your shop, but it's worked very well for quite a few places I've been in.

Second is during the actual interview. Auditors will quite often call you in to ask specific questions. Quite often, you have someone else in your dept sitting in with you to make sure everything goes well... just kind of a witness. When the auditor asks you questions here, you may answer them, but use as few words as possible. Never say 20 words when a yes will do. Treat this just like testifying in court. Answer the question asked, no more, no less. It's tempting sometimes to want to explain yourself or your reasoning for why something's done, but it's not relevant here. In the case from above, I would only hope the the DBA wouldn't answer like this:
Q: Do you backup the DBs every night?
A: Yes... but we quite often go several without our backups working, and we never test restores, and it doesn't matter anyway, because the drive we keep them on is old and slow and will probably die any day now, and since they're not pushed to tape we'd be in real trouble if that happened.

The clear answer is simply yes. Then SHUT UP!!!

Also, don't let them rope you into answering questions that are outside your area. Anything not having to do strictly with DBs is none of your concern. Some sample questions are...

Q: How many users inside Solomon have elevated rights to create accounts?
A: I'm not responsible for Solomon. The Solomon admin would have to field that question.

Q: What method do users use to authenticate to your intranet?
A: You'll have to ask that question to the intranet admin.

Q: How many users have db_owner in the ADP database?
A: At this point I could only guess, but send me that question in email and I'll get you an anwser.

Notice that last question was in your range and it still didn't get answered? Auditors will write down whatever you tell them on the spot, and move on. Don't guess at anything. If you're not sure of an answer, say so, and ask them to submit it in email and you'll verify the answer and send it to them. This is crucial because you won't get a 2nd chance to answer that once they've written something down. You've got a few dozen servers to look after, and nobody expects you to have all the answers off the top of your head.

Ok, that's all I've got... happy auditing!!

Hey guys... sorry it's been so long since I posted, but life stepped in, and then the holidays were upon us... the good news is I've got a real doozy(sp?) for you this time.

I'd like to talk about job steps and what makes them effective.

When you create a job, it's always best if it's doing some actual work. Of course, we all take that as a given, but it apparently isn't. We just picked up a new site to manage at work a couple weeks ago, and yesterday I got an email saying that the data mart loads were failing. Well, actually they're not failing, they're taking so long, the ops guys just cancel them. I asked how long they'd been like that, and they said "ever since we can remember". So what you're telling me then is that you've not only never had a data mart load finish, you waited 2 weeks after I started here to tell me about it. Ummm... ok, (that's another discussion I guess).

So anyway... I go to the jobs that control these loads and they have several steps, one of which pulls from a linked server. I figured that was the main culprit, but I was wrong. That step finishes in just under a minute. It's the rest of the steps that take so long... and here's why!!

All the other steps do is run a series of very long, very complicated selects. The selects themselves don't use any indexes, and they're running against millions of rows. Remember though, these are automated jobs, who's seeing the results of these selects? Nobody, that's who, but it doesn't stop it from running all these selects, and eating up resources. Oh, and did I tell you, they all have TABX locks hardcoded. So, not only are these useless queries taking up all the resources, they're locking like 11 tables for hours.

After explaining this to the guy, I get another email from him shortly after, and he said, so can you also look at why the reports won't run?
Needless to say, cutting those queries from the job fixed the entire problem. Well, almost, I added something like 20 indexes to the DB and it purrs like a kitten.

Ok, so here's the moral of the story... when you put a step in a job, make sure it's actually going to do something. I liken this incident to the time right after we got our ticketing system and we were telling our users they had to submit tickets to get work done. Across the board, they stopped sending us any email whatsoever. Instead, we were getting tickets for everything. Here is an example of some of the complicated problems we were assigned through the new ticketing system:

-I am going through the documentation so I'll know how the DTS process works.
-Didn't you tell me you weren't going to be here next week?
-Do you want me to send you that SP to look at?
-I got your email, thanks.

I only wish I were exaggerating. I actually flagged those tickets as they came in and pulled them up just now... I just knew they'd come in handy some day.

Anyway, my head's exploding so I'll see you guys next week.

OK, I realize that this can't be a perfect world, but why MS can't make a simple uninstaller that actually does what it's supposed to is beyond me. I have uninstalled Yukon Sept CTP on several boxes and always in the exact same order. Sometimes it works, sometimes it doesn't. Whenever it doesn't work, it leaves you unable to install RTM.
The usual fix for this is to reinstall the CTP, and uninstall it again. This for some reason manages to uninstall all the pieces that were left behind before. And of course, RTM doesn't provide an uninstall wizard so you can't use that.

One problem I recently ran across was that I wasn't able to install RTM, or reinstall CTP. The install for CTP said it had incompatible components and couldn't install. But the uninstall wizard said that there were no components to clean up. So now there's nothing left to do but uninstall manually.

But how do you know what to uninstall? The answer came to me from Donald Farmer at MS.

When you get this situation, check the log file under C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files
What you want is the *_Core.log. They should all have pretty much the same info in them as the same components are probably failing all the time.



Reading the log, you will find a reference to the CLSID (a guid) of the component that setup “thinks” is installed.

Here's a clip from mine.
Product "{982DB00A-9C4E-436B-8707-18E113BAA44C}" versioned 9.00.951 is not compatible with current builds of SQL Server.Expected at least version: 9.00.1399.06
The Product Name is ""
Product "{90032DD0-ABEE-4424-AC1E-B076BDD4E350}" versioned 9.00.852 is not compatible with current builds of SQL Server.Expected at least version: 9.00.1399.06
The Product Name is ""
Loaded DLL:C:\WINDOWS\system32\msi.dll Version:3.1.4000.2435
Error: Action "PerformSCCAction2" threw an exception during execution.
Return Code: 70032


Copy that guid to clipboard, search for it in the registry and purge the registry of every reference to it. Just highlight that long GUID inside the curly brackets, and you should be ok. I included the brackets in mine, but I haven't tested without it... though again, it should be ok.

I've seen one instance where this didn't clear it up either. It left a bunch of .manifest files in there I think. I'd have to check to be sure, but just delete everything in that same log folder and try again. That should work for you this time.

In the future, I sincerely hope that instead of just doing a check for incompatible components, they actually delete the entries as well. That would be really helpful.

This week I want to talk about what's been happening recently on SSC. As probably most of you know, I wrote the first in a series of articles on interviewing. In case you haven't heard of it, you can read it here.

During the discussion on this article, some of you said it must have been a joke, while others reverted back to the dark ages and performed a modern version of the Inquisition. I'll get to how I feel about that in a minute, but first I want to explain what the article was all about.

I wanted to say this so many times during the discussion, but I really wanted to see how far it would go... and originally, I was going to come clean in the 2nd installment, but I just don't think it can wait.
Let me say first off that this was a very carefully crafted piece. Didn't any of you catch onto the fact that the article was called "How to Mess Up an Interview"... GET IT?? I messed up the interview... (actually, one guy did get it... thanks, Paul, and while I think genius may be stretching it, I appreciate the vote of confidence.)

I've always had a flare for the dramatic, and when I conceived this series I had one thought in mind and that was to really drive the point home. So what I decided to do in this first installment was to give the readers just a little something to show them the kinds of things that can start out innocently enough but then completely blow an interview. The published draft of the piece was probably my 7th rewrite. Believe it or not, I actually got the idea from one of the worst interviews I ever had the pleasure to conduct. This guy went off on how dumb his current employer was, and how he was the only one there who knew anything at all. In the course of answering a question about the biggest disaster he had ever been a part of, he had a few choice things to say about the people he worked with including referring to the NOC guy as a stupid N-word... 3 TIMES!!!
When I asked him about his team and how they work together, he referred to his fellow DBAs as Jesus freaks who would just assume hold hands around the server and sing Kumbaya than to actually learn anything to actually fix the problem. It amazes me sometimes the lengths people will go to keep you from hiring them.

So anyway, I wanted to write this first installment in a way that would completely fail an interview. I started with that guy's interview as a model, but I had to be more subtle. There's no way I could possibly say the things he said, so I wrote one draft after another trying to tone it down to not only something believable, but something that would get my point across. In other words, I wanted to be that guy you wouldn't hire because he's just a little too... off, I guess.

The point of that first piece was to show that regardless of what you have to say, if you say it wrong, nobody will listen. There was some good solid advice in that first installment, but many of you refused to even see it because of the way it was presented.

Some of you weren't offended at all. You are my main audience. Others just didn't listen because they didn't like the way it was presented, but they kept their professionalism and just stated that they disapproved and went on about their business. You are also my audience. However, the hardcore zealots out there who not only denounced me and my writing, but also SSC as a whole, well, you are not my audience, and frankly, I'm ashamed to be in the same community with you. You embarrassed yourselves, and you embarrassed us all with your childish and petty behavior. Imagine professional people going on a public site and making a spectacle of yourselves like you did... and even cursing. You were so self-righteous, and smug, and proper, that you forgot how to conduct yourselves in public.

And for those of you who had Steve remove you from the list because of it... I say good riddance. If you're going to be that way we don't want you anyway, and you're only hurting yourselves. Personally, I enjoy getting my SSC newsletter every day, and there have been a lot of good articles. If you guys want to screw yourselves out of the knowledge you get from SSC because of one carefully-crafted piece, then you deserve what you get.

Well, let me just say that I've proven my point.

I did get a lot of support privately though. Many of you wrote me to express your support. I want to thank each and every one of you who did that. And those of you who supported me publicly, I thank you too. I didn't think anything I said was offensive either, but apparently, that's the kind of thing you can't dictate.

That's fine though... you guys turned a simple article into some kind of holy war and gay-bashing party.

And by the way... that gay thing really wasn't a slam on homosexuals. It's just an idiom that some people use. You know, like those other literal idioms like 'pulling your leg', 'heart of gold', 'broken heart', 'be an a-hole', 'look a gift horse in the mouth', etc. And the point of putting in the piece was to show you in the next installment that you have to re-train yourself to not use common phrases that could possibly offend. I got called down at work once for using the phrase 'partners in crime'. You just never know what will set somebody off (evidently).

So now it's up to you guys... do you want to see the rest of the series or not???
I await your response.