Tag Archives: blunders

Admin

My favorite vendor code.

1 Comment

I was given this code the other day to review before it gets run against prod.  It’s code that a vendor wrote to help us clean-up some of the bad and old records in this one app table.

There’s really not much to say about it except that it’s by far the best vendor code that’s crossed my desk in a long time.

I hope you enjoy it as much as I do.

 BEGIN TRANTable1
SET touchedwhen = GETDATE(),
touchedby = 'mysupport',
statuscode = 'CISPA'
WHERE createdwhen < '2009-01-01 00:00:00.000' AND statuscode = 'AWDP'
-- Where createwhen < getdate()-1 and statuscode = 'AWDP'

ROLLBACK
COMMIT

So once I got this and the email chain I started based off of it was priceless.  I don’t feel really right about printing the email chain here, but rest assured that I’ve defended the logic of this query perfectly.

Admin

Who’s afraid of cursors?

1 Comment

Ok, so one of my last posts talked about using a cursor for something and I came upon some criticism and/or some wincing about the fact that I used that horrible word.  However, cursors like anything have gotten a bad rap (xp_cmdshell is another).  People, there’s nothing wrong with using cursors as long as it’s what’s actually needed.  The problem comes with frontend coders writing cursors for everything and severely abusing this feature. 

But there’s nothing inherently wrong with them… esp for admin tasks.  There are so many legitimate uses for cursors I can’t even come close to naming them all, but here are a few…

Doing something to every DB on a server.

Doing something to every schema in a DB.

Doing something to every table in a schema.

Doing something to every user/login acct (like running changeUsersLogin to correct a ghosted user acct issue).

These are all perfectly legitimate uses of a cursor, whether it’s T-SQL or powershell.  So let’s get ove rthis hangup we’ve got against both the concept and the word.  There’s nothing wrong with it as long as you do it right.  Afterall, what is a cursor anyway?  It’s a way to store a list of values so you can perform an action on each one of them.  And the alternative is to hardcode each one of those lines yourself or use the GUI for everything and we all know that’s completely unacceptable. 

And in powershell everything you do is a cursor… even things that don’t need them.  Of course, they call it a foreach loop, but that’s just a more acceptable name for a cursor isn’t it?  It’s more specific… foreach one of these things, I want to do this…  hell, even almost every backup routine I’ve seen in the shops I’ve been in, and on the web cursor through the DBs on a server and back them up.

So deciding to not use cursors (or at least wince at them) because they get misused by some is like refusing to use a hammer on a nail because of someone you saw trying to open a banana with one.  It wasn’t the right tool for the job so it messed things up.  But it IS the right tool for a nail.

Admin

Should you be in Simple or Full Recovery mode?

5 Comments

Lately I’ve been bitching about vendors in my Marklar blog, but I thought I’d switch things up a bit this time and do some complaining here instead.  So this is a rough conversation I had with a vendor like 2 days ago.

Me:  The prod DB is twice the size of the archive DB.

Vendor:  That’s because the prod DB is in simple mode.

Me:  I don’t understand.

Vendor:  Well, DBs have 2 modes you can use to control when the log files get truncated.  Simple mode is… (interrupted)

Me:  Yeah, I know what the recovery models are.  I’m a DBA.  What I want to know is how the recovery model being in simple means that the prod DB is larger than the archive DB.

Vendor:  Well that’s what I was trying to explain to you.  When your DB is in full mode,  you can actually control when your log is truncated and you’ve got finer control over the size so you could go in there right now and truncate it.

Me:  But it’s in simple mode now so it truncates whenever it checkpoints.

Vendor:  EXACTLY.  So you have no control over that and that’s why prod is bigger.

Me:  That makes no sense.  Simple mode keeps the log truncated so you don’t have to control it.  It’s always truncating it.  So how does that make the DB bigger?

Vendor:  You don’t understand what’s going on here.

Me:  No, actually I don’t.  Why don’t you educate me.

Vendor:  Well like you said, you’re a DBA, so you should already know this stuff, but I’ll be happy to go over it again.

Me:  (laughs a little) Yeah, I’d love for you to do that.  I didn’t get it all written down last time.  Go ahead, I’m taking notes.

Vendor:  When you’re in full mode, you can take log backups and that will truncate your logs.  So you can control how big your logs get, and therefore your DB, by taking log backups at certain times.  You don’t have that option with simple mode.  You can’t take log backups, so you can’t tell it to truncate the logs.

Me:  I see… so if you’re in simple mode how does the log get truncated?

Vendor:  It truncates it automatically.

Me:  Ok, so if it truncates it automatically, when does it do it?  Is it on a schedule or what?

Vendor:  I really don’t know, I just know you can’t tell it to truncate it.

Me:  It truncates it whenever the DB writes a checkpoint.  So basically after every transaction.

Vendor:  Ok.

Me:  So since it’s almost always truncating, how does that make my prod DB bigger than the archive?

Vendor:  You still don’t get it?

Me:  Nope.

Vendor:  Well, we’ve got to get through this, maybe we’ll have time to get into it later.  For now, I’d like you to go ahead and have you switch it to full mode so we can get the size of the prod DB down.

Me:  No, I’m not going to do that.  That’s just stupid and it makes no sense.

Vendor:  Well, we can’t go any further until you do. 

Me:  Why not?

Vendor:  Because the prod DB is too big and this operation will take a long time. 

Me:  And how are the updates we’re doing on these tables effected by the size of the log.

Vendor:  Because it’s a lot more data to move.

Me:  But we’re not moving any data.  We’re only updating data.

Vendor:  Well, like I said, we don’t have time to get into all of this right now.  I don’t have time to teach you how all this works.  Just go ahead and switch to full mode and you’ll see what I mean.

Me:  No I won’t see what you mean because it’s stupid and it only shows you have no idea what you’re doing.  So you let me manage the space and my backups, and you worry about the updates.  And this is only a 4GB database anyway so it’s not like anything we do will take hours.  We’re only talking about the difference between a few seconds.  And there’s no way how much data is in the log will have any effect on how fast updates are.  So you just do your thing and leave me to do my thing.

OK, so that’s what I did the other night.  And it seems like I’m having these types of talks more and more with vendors.  It’s incredible isn’t it? 

Anyway, I hope you guys enjoyed this.  And for the record, simple mode truncates the log whenever the DB checkpoints so it’s always truncated.  And full mode needs a log backup to be able to truncate.  In case some of you were confused as to why this was a dumb conversation.  It’s actually dumb on a number of levels.

Admin

You’re the center of my universe

2 Comments

It will never cease to amaze me how people can be so narrow in focus.  It never even crosses their minds most of the time that someone who supports them may not be working right beside them all day long and may not be involved in their day to day activities enough to pick up on all their slang and terms.

So when I get a group of users like that I always have to laugh a little as I set them straight.  Yes, I’m the DBA for this company.  Yes I do backups for the servers in my control.  And yes I’m the one you need to call whenever you need anything at all SQL related.

However…

NO — I don’t have all your servernames memorized.

NO — I don’t know how big your DB is off the top of my head.

NO — I can’t tell you what rights everyone in your group has.

NO — Sending me a ticket that says “export my favorite tables” will not get you what you want.

NO — I don’t remember that conversation you had with the vendor about what we need to do to upgrade your DB.

NO — I didn’t realize that your app was renamed 2yrs before I got here and half of your group still refers to it as the old one.

So when you guys out there (and you know who you are) send requests to your DBA team,  know that they’re here to help you but you have to know that many times they’ve got several hundred servers to monitor and they can’t always keep your info on the top of their heads.  So at the very least whenever you send them a request, include the ServerName, DBName, etc with it.  They need to know what you’re talking about.

Admin

Have you had a chance to do this?

3 Comments

Users can really stick it to you sometimes and today I got caught on a call with a user who I suppose was trying to stick it to me somehow. There were about 9 of us on the call and she asked me:
Have you had a chance to look at the old backup files yet?

And I was like, what old backup files?
She said, I wanted you to go through and make sure there aren’t any old backup files sitting out there from 3yrs ago taking up space. We’ll want all that stuff cleaned up before we switch servers.

I said, well was I supposed to do that? Is that something you’ve asked me to do?
She said, no, I was just wondering if you’ve had a chance to do it yet.
I said, yeah I’ve had many chances, but since nobody has asked me to then I haven’t done it. I’ve also had many chances to eat bananas and grind up cherries with a sharpie, but nobody’s asked me to do that either.
Would you like me to go look for extra backup files?

Yes, she said.

I said, ok then why didn’t you just ask me to instead of trying to make it look like I’m not doing something I’m supposed to do? I’ve had many chances to do many things including delete all the data in your system, but since nobody’s asked me to, I haven’t done that.

Well, she said, I was just trying to find out if you’ve had a chance to do that yet.
Yes, I get that, but you haven’t asked me to do it yet so how could I have had a chance? Now you in here trying to make it look like you’ve asked me several times and I’ve just been slacking.

OK, so that’s basically how it went. Dealing with users sometimes can really be hard… especially when they forget a key component and then try to cover it up like it’s been on your tasklist forever. And something this trivial is just ridiculous. It takes 5mins to check for old backups and admitting that you forgot until now is no big deal. Sometimes we spend more time on CYA than actual work.

Admin

When is a ticket not a ticket?

I get this from time to time where a user will open a helpdesk ticket and assign it to me or my team and it has nothing to do with anything. Apparently it’s rather difficult to get users to understand what a ticketing system is all about.

Let’s take the ticket I got this morning. Here’s the exact wording pasted directly from the ticket itself:

I have a question about SQL.

Typically whenever I get a ticket like this I close the ticket immediately with the response:
OK.

For some reason today though I was feeling more mellow than usual so I wrote the user to inform him what ticketing systems are for and why that wasn’t really an appropriate ticket. Then I asked him what his question was and that if he really felt the need to put something like that in a ticket, to at least print the question with it. Of course I’ve heard nothing back from him. I did close the ticket however.

Like I said above… I get this from time to time at my various gigs and it’s really hard for someone like me to not be a complete smartass about it. I got one at my last gig that said:
I looked up what you were telling me about indexes and you were right.

Now, while I suppose I have no real problem with you creating a ticket to officially document that I was right about something, don’t assign it to me. Assign it to my boss.
I just wanted to officially document that Sean was right about one of the basic principles of indexes that he discussed with me. He should not only get a raise, but should have his pick of your daughters.

So now that I’ve ranted a little, here’s how to correct the situation. And this is to you users out there… tickets are for verbs… stuff you actually want us to do for you. You’re free to come by or send a regular email if you want to praise us for being right, or to ask a question because we’ve always been right in the past. But unless you have something for us to actually do, keep it out of the helpdesk system.

Admin, Powershell, Time-savers

How to save your life with powershell.

3 Comments

I’m always preaching the wonders of powershell, but I think quite often I forget to stress the impact that it can provide. And today is one of those times when I’m able to give you a real life impact of how PS can save your life… ok, your job anyway.

One of the things PS does really well is scripting DB objects. It’s always one of the first things I show that’s really useful in my classes and it always gets a appropriate amount of gasp. But other than creating dev systems from prod, what good is it? Well, it’s a fabulous way to script your prod environment on a regular basis just to have the scripts handy. If you’re able to use PS to easily script your tables, SPs, views, users, etc. then you’re going to have a history of your DB that you can pull from anytime. And if you’re smart enough to store those scripts in txt files with dates appended to them, then you can always get back a complete version of your DB for any given day. Cool huh?

And the implications of this are huge. Not only can you create a complete environment for any given day so that you can compare it with the current version, or any other version you like, but much more likely is the scenario where someone makes a mistake in prod and needs some small piece of code replaced. So someone drops an SP, or alters a view, or kills a job, or anything else along those lines. Of course, this won’t help you with data accidents, but imagine the possibility of being able to pull your butt out of the fire because you were able to recover from a mistake you made… or anyone else made.

This came not only in handy, but actually saved my ass at my last gig where I had these scripts running and accidentally dropped all the SPs in our prod DB. I could have sworn I was on dev, right? So I had my PS scripts to import the objects again too, so as soon as I realized my mistake I recreated all the SPs and all was well with only a minor hiccup to our reporting cycle. But that’s exactly what I’m talking about. If you have these things ready to go you’re ready for whatever kind of disaster comes up and if you not only have the scripted objects sitting there, but also the scripts to put them back, then you’re in the best shape you can possibly be in.

My favorite way to store these like I said is in txt files.
I put them in a folder named for the DB, then another subfolder after the object type, then I name the files by schema.objectname.txt. So a sample path to an object would look like this:
c:\scripts\DB1\SPs\dbo.SP120100714.txt

Now to parse the date back out is a simple matter and can be handled with a simple PS script. And the scripts you need to restore can be as easy or complicated as you like. You can have any or none of the following:

Restore all tables
Restore all indexes
Restore all SPs
Restore all views
Restore single SP
Restore single view
etc…

You could also combine them into a single PS script more or less that you just pass an object type to and it figures out the path and does the restore for you that way. Either way is good really.

So setup your PS scripts to script your DB objects and store them someplace. It’s only txt so you can keep them pretty far back if you like, and even archive them in zipped files if you like.

Good luck.

Admin

The gimme question

3 Comments

I’m in the middle of a full-blown round of interviewing for a new DBA and I’m in the middle of the initial phone screening process. One of the things I used to do is give someone a gimme question at the end so they end on a high note. Quite often they don’t do as well as they would like so they end up saying I don’t know a lot more than they usually would. So the gimme question is a way to let them feel a little better. The problem is that quite often I’ll ask my gimme, and they’ll get it wrong. Now these are easy questions that someone whose a DBA should easily know. So it backfires on me more often than not.

However, lately I’ve got a new favorite type of gimme question. What I’ll do is let them give me the question they want me to ask them. So I’ll say something like… OK, I’ve got one more question and you get to pick it. So you come up with a question that you want me to ask you and I’ll ask it.
And once they get over the shock of what I’m actually saying they think for a minute and come up with a good tech question. So I ask them their tech question and what surprises me every single time is the amount of times these people get it wrong!!! I don’t understand that. You got to pick your own question and you picked something you can’t even answer?

The funny thing is, I really don’t even care what you pick. There are 2 ways you can go, right? You can go the really easy route and have me ask something like How do you spell T-SQL? Or you could go the complicated route and try to impress me with your knowledge and have me ask you something really advanced.

And to this day I haven’t had anyone really try to impress me, but nobody’s gone the really easy route either.

I don’t know about you, but I’d say this question is effective on a number of levels.

And I really don’t care what the question is, but you’d better be able to answer it.

Admin

Why you have to be on top of your game

10 Comments

I just heard from a DBA at my last gig who was keeping me in the loop about things I had done while I was there.
He said that they had to take away the 2 1TB LUNs I had them put on our DB server because they caused performance problems. The SAN guys warned us that having LUNs that big would hurt us and I guess they were right. Well, not really. This was the report I got back from the DBA, so not my words. When I grilled him about it, he did say that they had given us a single spindle that was 1TB for each of them instead of giving us several drives in the array.
So let me get this right… I asked for 1TB LUNs, and instead of doing it right you gave me a single drive and used it as justification for showing me you know more about disks than I do? I find that incredible.

So guys, yeah… you really have to be on top of your game and know your stuff so that when they do stuff like this you can call them on it. And I must admit that I’m not sure it would have crossed my mind to even check that it was a single spindle. Why would that even cross my mind? These are enterprise SAN guys… they know their way around and should definitely know better. What I didn’t realize was that they were into playing games. It just goes to show that you try so hard to be effective in some places and they just won’t let you.

Uncategorized

The xp_CMDShell Curse

I interviewed a couple guys for a DBA position the other day and I asked one of my usual questions which is simply to discuss xp_cmdshell and its implications.  Without hesitation, I got the same answer twice.  Both guys gasped and talked about the evils of xp_cmdshell.  So I let them go on and then presented them with a couple scenarios that we discussed.  And again, all of this just makes me think about true evil and how xp_cmdshell just doesn’t fit.  See, this xp was developed for a reason;  It filled in the gap on some missing functionality, and it does it very well.  In fact, it does it so well, and the gap is so big, that xp_cmdshell gained in popularity and became an instant success.  And in fact, it was used too much and this is where it got its bad reputation.  Don’t hold that against xp_cmdshell though.  Just because everyone recognizes its usefulness doesn’t mean that it’s bad.  The problem started because everyone wanted to use it and the security was opened wide allowing damage to be done. 

But it’s not evil;  it’s just misused.  If you keep it locked down there’s no reason why you can’t have a fairly nice relationship with xp_cmdshell.  The same goes for cursors.  Cursors have also fallen prey to the cmdshell curse.  And again, it’s through misuse. So don’t kick either of these out of your shop because so many other devs are stupid.  Use them both wisely and they’ll do good things for you.