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.