Tuesday, April 7, 2009

For the Love of All That is Good and Pure, tempdb is Your Friend!

“Developer, meet tempdb. tempdb, meet Developer. What’s that, DBA? You’ve never met either? Oh, how rude I’ve been. Allow me to introduce you….”

Well…Developer and DBA…I’m sure you’re wondering why I’ve called you in today. I’ll get to that, but first, let me ask you one thing – have either of you ever heard of the “master” database?

Don’t “of course” me….

Well, since you both clearly understand the importance of the master database, let’s get down to business. Did you know that our production server’s master database has a hundred and thirty six additional tables in it? Oh, you didn’t? Funny that….

Why do you think that might be?

No….not a failed service pack. Nope, nothing to do with replication. No, parallel query plans don’t just magically create tables. Lousy guesses though – I’ll give you points for desperation. Give up?

Well, if you look at the dates that those tables were created on, do they ring a bell? That’s right – it looks like they were created out on the same day that the latest version of our production app was rolled out. Any guesses yet?

What’s that, Developer? You say that DBA must have forgotten to change the database in their query pane before they ran the script you gave them. Hmmmm…

DBA? Oh…all of the scripts are supposed to contain USE statements, which Developer obviously forgot. I seem to remember a policy like that. So, did you check to make sure that they were in there? No. But you wondered why none of the tables showed up in production the first time you ran the script. Thank heavens for that. There’s no getting anything past you…

OK, well being as both of you seem to be having fairly regular episodes of mental flatulence lately, how about I suggest a solution.

DBA, I want you to change the default database for all of the administrative accounts to tempdb. Why? Huh-boy…OK, try to follow the bouncing ball. No tweeting for the next thirty seconds, mmmkay? If your default database is tempdb, on the fairly substantial chance that you forget to change the database, and that Developer forgets to code in USE statements, all of the objects in the script will get created in tempdb. As I’m sure you both know, tempdb gets recreated every time SQL Server starts up. No, I’m not kidding. No, it’s not a new feature in SQL 2008. Look it up if you don’t believe me. NOT NOW…step away from the BlackBerry.

Now being as you’re both such attentive and diligent employees, I should be able to count on the fact that all new admin logins will have their default database set to tempdb, right? And I should be able to count on the fact that we’re actually going to take the time to write in that pesky USE statement, right?

Yeah, I’ve got my doubts too. I somehow knew that would be your answer….which is why I took the liberty of creating a DDL trigger in the master database. Any time one of your objects gets created in master, my trigger will roll back the action. Wait – there’s more. Not only will it roll back the action, but it will send a service broker message to a queue that I’ve setup in a database called “Retribution”. The activation proc for that queue calls a CLR function. As you both know, you can do almost anything in the CLR. This function is a doozie. Ever heard of Terry Tate? Nuff said.

You’re welcome. I’m glad we’ve had this talk.

1 comment:

Kay said...

Just had a similar incident yesterday when I mistakenly trusted (read: didn't check) a developer's work and ran his scripts over the master database, luckily in dev. Took me another 2 hours to fix the stuff-up. Wish I read this post earlier...