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.

MidnightDBA News

Well, you get Tori.

6 Comments

As many of you know I spent the weekend in Red Stick at SQL Saturday #28.  It was held on LSU campus and I personally had a great time.  They had about 650 signed up and about 350 show up, but the size of the venue made it seem like there was hardly anybody there.  I don’t remember a time when it was impossible to walk around.  The event was put on really well and I love the black speaker shirts with our names on the sleeve.  And the only really criticism I’ll offer is for them to put more effort into the food next time.  Breakfast was almost horrendous and lunch was barely passable.  The best thing I can say about the food was that the cookies they got for the snacks were actually pretty good.  I think they were grocery store cookies, but they were big and soft and actually rather good.  I, as well as some others I talked to, were pretty surprised by the cookies.

The biggest thing that happened to me this weekend is something that happened to me again and again.  Everyone apparently would rather have had Jen there instead of me.  Every time I met someone, they’d say, so is Jen here?  It usually went something like… oh you’re with MidnightDBA?  Is Jen here?  I only didn’t get that from 2 people I talked to.  And I believe those were Patrick LeBlanc and Wes Brown.  And it got me thinking… it’s like going to an event and having them announce that a Mythbuster is coming.  Then the crowd starts chanting Ja-mie, Ja-mie, Ja-mie… and then Tori steps out.  So you went through the trouble of getting a Mythbuster and all you could get was Tori?

So I guess I’m the Tori of Midnight, but you know what… sometimes you  get Tori.  Maybe I’ll try to bring Jamie next time.

MidnightDBA News

Going to Red Stick

2 Comments

Ok everybody, I’m off to SQLSaturday #28 in Baton Rouge tomorrow.  I hope to see as many of you there as I can.

If you see me and you want to come up and say hi, then by all means do.  I love talking to readers.

Oh, and a little trivia on Baton Rouge.  When the french settlers were scouting for a good place to start a town they were scouting from boats.  They came across this patch of land they liked and the Indians had already marked it with a big wooden pole that they painted red at the top.  So they referred to it as the place marked by the baton rouge.  And they just decided to name it that.

As far as I can tell that’s the real story.  I work with a guy who took Louisana history and this is what he was told in class.

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

Being the Boss is Hard Sometimes

7 Comments

Sometimes the difference between being a boss and a regular DBA shines through in the worst way. 

I’m interviewing a few guys right now for a position I have open on my team and I’ll say I’m getting all walks of life.

I’m getting everything from guys with lots of experience to those just wanting to get started.  This post is really about th guys with lots of experience though.  More often than not what I see in guys with lots of experience is that they’ve never bothered really learning DBs very well.  I spend most of the time I’m talking to them trying to figure out questions that are low enough for them to be able to answer.  One guy in particular comes to mind.  I don’t know if he reads my blog or not, but I guess we’ll find out huh?

This is really a unique position because I’ve interviewed him several times in the past few years.  In fact, every time I have an opening he applies.  The problem is that he’s always demonstrated on a perfunctory knowledge of SQL and this last time I was hoping for more, but he hasn’t improved at all that I can tell.  In fact, he gave me all the opposite answers I needed.  Always use @tables instead of #tables.  Never use ‘select into’ because it’s a lazy way to code.  You should use ‘insert into’ instead.  Those were some specific answers he gave me this last go around.  Unfortunately those are the same things he said last time too.  So while he’s been working all this time, he doesn’t know any more than he did like 3-5yrs ago. 

So this time I really felt compelled to write him and tell him how bad he was.  I don’t do this kind of thing often, but since I’ve talked to him so many times and I’ve specifically seen how he hasn’t improved, I wanted to be honest with him in the hopes that it might spur him into doing something about it.  So I sent a very honest email and of course these things always sound very harsh but what can I do? 

So it’s times like this when being the one who’s in the position to tell people yes or no means sometimes you have to hurt someone’s feelings by telling them the truth.

How about you guys… have you ever had to tell someone the honest truth like that?  How did it go?

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

How’s business?

My current gig is really starting to open up in terms of how busy we are. Users are starting to come to the DBAs more and more for things and the outlook is bright. It’s taken a while for them to start coming to us because the DBA group before wasn’t very responsive and it really turned a lot of the users off to coming to them. But now it’s like they’ve been love starved for so long and now they’re finally starting to get what they need cause we’re overflowing with requests.
Boy, what a time to be a DBA down, huh?

Instead of working, I blog.