A SQL Quiz – Calling you out!

So Chris Shaw called me on a SQL Quiz. I like it. It is so much less painful to learn from someone else's mistakes than your own.  Here are the details.

<snip>

I started to think about this and I am going to try to start a game of tag. So here is the way it works, for many new DBA’s they may not realize that all of us have made mistakes and that our mistakes can be… rather stupid. I challenge each of the people I tag in my blog to post as least one mistake that they recently made I will start by describing 2 mistakes that I made. One of my mistakes was as a junior DBA and one about 6 months ago. The point of this game is to in no means embarrass ourselves or discredit ourselves. But more of a learning experience from our mistakes type of a deal.

</snip>

Before I start, let take this opportunity to look at what could result from a DBA’s mistake.

• Loss of data
• Downtime
• Exposing sensitive data
• Performance Degradation

No pressure, right?

1.

The first big mistake I made was pretty bad. I almost gave up SQL Server. It was right after SQL Server 7.0 came out. A new customer was trying to move there data to their hosted application. It was a few tables. <Lightbulb> Hey, that sounds like a good use for that new DTS thingymabobber <Lightbulb /> So I go through the DTS wizard and it fails after creating the schema. I run the wizard again and it works EXCEPT I got the source and destination backwards. Now, they have empty schemas on both sides. Since the source was their development environment, they didn’t have backups. Of course.

What I learned: Always check for backups. No matter what you do, have a backup you can trust. That goes beyond disk backups sometimes. Copy them to somewhere on the network if you are working on the storage. Do a SELECT INTO before you do that adhoc DELETE.  Srsly, don’t be that guy.

2.

We were having ongoing disk IO issues. Later on that night, I am adding a disk array. However, while poking around I discover that the stripe size of the existing data drive is 8k. It looks like I can easily “migrate” it to 64k. I quote migrate because that is what it says in the HP management software. That doesn’t sound destructive does it? Psst, it is. A few hours later, the database had been restored but we were sector aligned and had a had a 64k stripe size. That doesn’t sound so bad except the application cannot afford a couple hours of down time even for a free 10% of disk performance.

What I learned: Don’t be a cowboy. Up until then, it was on the job training. I learned it as soon as it broke and I had to fix it. It is a good way to learn but a bad way to DBA. All changes should be planned, tested, executed or rolled back. A cowboy DBA may shine 99% of the time but that one time can blow the SLA for the year.

Runner ups:

• Not backing up the “mission critical” database tmpTest.
• Checking that “Boost Priority” box.
• Not adding a local account before messing with the machine account. Locking myself out of windows on a critical SQL Server.
• Adding columns through the GUI. Don’t. Just don’t.
• Adding a duplicate clustered index because I could not figure how to change the primary key from nonclustered in the GUI.

Who am I calling out? You. If you are reading this, I am throwing down the gauntlet. I shall name names too by picking on the recent commentators.

K. Brian Kelly

Marlon Ribunal

Mladen

Ted Malone

Rhys

Tom

Jimmy May, Aspiring Geek

Michael O'Neill

Ludovico Caldara

Adam Machanic

Aaron Lowe

I was also asked to plug, http://sqlfool.com , by someone other than Michelle so I will call her out too.

email it! |   |   |   |  | 

Permalink     12 Comments  

Rate this Post: 12345
COMMENTS:

"Adding columns through the GUI. Don’t. Just don’t." Can you explain why?

posted @ Thursday, November 06, 2008 2:22 PM by Steve Withington

---

Let's see..... ;-)

not highlighting the where clause in a delete statement
shutdown instead of logoff
checking "drop dependent objects" when scripting a proc which dropped the tables on production
hitting F5(run) instead of F6(parse)

I am sure there is more

posted @ Thursday, November 06, 2008 2:34 PM by SQLDenis

---

Steve,
The GUI creates a temp table, copies all of the data to it, recreates the table and copies the data back. In tsql, you can add\modify columns and most of the time, it is just a metadata change.

There is a good 2 part post on schema changes at http://blogs.msdn.com/sqlcat.

They may have changed the behavior in SQL 2008.

JM

posted @ Thursday, November 06, 2008 2:44 PM by JasonMassie

---

@Jason,
Ah, thanks! I didn't realize that the GUI created a temp table.

posted @ Thursday, November 06, 2008 3:33 PM by Steve Withington

---

Thanks Jason! ;) I've posted my response (humbling confession) over at [URL]http://www.tech4him.com/content/sql-quiz-follow[/URL]

DBA's can't get complacent. The daily checking of DB's, job statuses, maintenance plans etc... bit me recently. I now have a shining three ring binder on my desk. ;) Of course I don't consider myself a DBA either. Ha!

SQLDenis - Yup, that's a good one. Done that before too.

One more from me: Forgetting to copy the WHERE clause back from another query window before running an UPDATE query. Doh!

posted @ Thursday, November 06, 2008 6:20 PM by Tom

---

Hmm, I'll have to think hard about this one. Lots of little screwups over the course of my career, of course, but *knocking on wood* very few major ones. This is mainly because I've rarely been a production DBA -- as a result most of my screwups were in dev or QA environments and so I'm happy to say that I've never managed to cause production downtime (or at least, I can't remember doing so). That said, I have caused dev downtime and that's just as bad when you have a whole team of people not able to work for half a day. Or even worse, one time I and several other devs were pulling an emergency late night session and I caused the dev server to become unavailable at around 10:00 p.m., while a huge rollback chugged along that should never have started to begin with. I was not popular for a few days after that one. Anyway, I'll reply formally on my blog soon. Thanks (I think?) for the call out...

posted @ Thursday, November 06, 2008 6:54 PM by Adam Machanic

---

Comments from the following blog entry: A SQL Quiz - Follow Up, located at: http://www.tech4him.com/content/sql-quiz-follow

posted @ Thursday, November 06, 2008 7:03 PM by Tech4Him

---

Comments from the following blog entry: A SQL Quiz - Follow Up, located at: http://www.tech4him.com/content/sql-quiz-follow

posted @ Friday, November 07, 2008 9:26 AM by Tech4Him

---

Comments from the following blog entry: SQL Quiz - Mistakes You've Made?, located at: http://www.sqlservercentral.com/blogs/brian_kelley/archive/2008/11/07/sql-quiz-mistakes-you-ve-made.aspx

posted @ Friday, November 07, 2008 10:53 AM by K. Brian Kelley - Databases, Infrastructure, and Security

---

Thanks for the plug! I've posted the gory details of my worst work day here: [URL]http://sqlfool.com/?p=132[/URL]

posted @ Friday, November 07, 2008 12:14 PM by Michelle Ufford

---

Well Jason! These are my top 3 mistakes on SQL Server order by relevance ASC, all related to backups:

Mistake) 3 years ago I added a DB in recovery mode simple on a Server with a maintenance plan backing up all user db transaction logs
Effect) The maintenance plan started to fail because a non full recovery database was found and I realized it after the disks were filled up.
Bare Solution) Taken the database in recovery mode full.

Mistake) 3 years ago I restored a database with a new name, I checked it was ok and then deleted the wrong one and renamed the restored one to give it the original name.
Effect) The database was backed up by a maintenance plan. After the restore the database has been unchecked from the backup list and I realize 6 months later I wasn't running backups.
Bare Solution) Re-checked the database inclusion in the maintenance plan.

Mistake) 3 years ago I implemented a backup maintenance plan without checking the verify option
Effect) I needed to restore the DB but the backup was corrupt.
Bare Solution) No solution, I regret I loose the database....

Finest Solution 1) 3 years ago I wrote custom stored procedures to make backups + restore with verify_only for all the running databases. No more maintenance plans, no more backup mistakes.
Finest Solution 2) 2 years ago I implemented a script on a linux box that crosschecks ALL BACKUPS ON A HUNDRED SQL SERVERS MY DBA TEAM MANAGES by querying msdb: When some backup fails to complete I realize it by reading a morning report. No matter how they are backed up.

posted @ Saturday, November 08, 2008 3:56 PM by Ludovico Caldara

---

Comments from the following blog entry: http://blog.tech4him.com/2008/11/a-sql-quiz-follow-up/

posted @ Wednesday, January 21, 2009 7:32 PM

---

Name (required)

Email (required)

Website

Notify me of followup comments via e-mail

Simple BBCode can be used like [url=http://example.com]Example[/url] and [B]