Jeremiah Peschka, SQL Server Developer

Brent Ozar has tagged me to share two mistakes I’ve made as a DBA throughout my career. (Apparently this was started by Chris Shaw.)

Mistake the first: DELETE without WHERE. Like Brent (and every other person on the planet), I ran a delete without a where clause.

A few years ago, I had a consulting gig at a local utility company doing application development. This app was a relatively sophisticated homegrown CRM application that handled all customer interaction. The call center reps logged into it every morning when they sat down and logged out every night.

It was still very early in my career as a data mongoose and I was exceptionally inexperienced in the ways of properly executing SQL jobs (I was putting BEGIN TRANSACTION … ROLLBACK TRANSACTION around my SELECT statements until someone told me different). Some bad/duplicate customer data had made its way into the database and it had to be deleted right now. Being the good little developer that I am, I immediate whipped up a delete script, on the dev system and properly hidden inside a BEGIN TRANSACTION … ROLLBACK TRANSACTION block, and wrote a select statement to verify that my SQL did exactly what I wanted it to do. The first time around it didn’t, so I deleted a few lines and didn’t bother cleaning up my code formatting because I was in a hurry. After a few iterations, the delete was working perfectly, my manager verified it and told me to go ahead and run it.

So far so good, right? Well, a few days earlier, another developer had showed me that you could highlight text in the query window and press F5 to run the query. This way I didn’t have to keep changing my code to say COMMIT TRANSACTION instead of ROLLBACK TRANSACTION. I switched my connection over to the production machine, highlighted what I thought was my code, and hit F5. I walked away to grab a drink from the fridge.

When I got back to my cube the query was still running. It turns out I failed to highlight my entire query due my laziness in cleaning up my formatting and my overconfidence that my query was going to work correctly. Instead of deleting a few customers from production, I was deleting all customers from production. On Monday. An hour before lunch.

Anyone who has ever worked in a call center can tell you that Mondays are very busy days.

Long(ish) story short, we managed to restore from that morning’s back up, but not before all of the people on the call center floor knew that I was responsible for the outage and I was the reason they would have to write down all of the customer complaints by hand and then enter everything in the system as fast as they could during their idle time.

Moral of the story: Check everything you do at least 7 times. Paranoia is key to not deleting all customer records on a Monday.

Mistake the second: Not verifying data. I’m really not sure what to call this apart from just plain stupid.

We’re currently waiting for a data load process to be finalized so that replication can be turned on. In the meantime, I’m developing against partial, stale, data. My manager and I decided, in our infinite wisdom, to script the database, drop it, rebuild it, and then import the data from the publisher database. It was a fairly sane idea, relatively speaking. We went ahead and did this, everything was successful, and we went home, content in the knowledge that we had done a good job.

The next day, I start working on stored procedures again and I notice that conditions are occurring that shouldn’t ever happen in this data. Bills aren’t lining up with the services on them, accounts are reporting incorrect data, the associations were just completely incorrect. It looks like, somewhere along the line, when selecting the tables for import one of us didn’t check the “Enable Identity Insert” box. All of the records inserted correctly, but the keys didn’t line up.

The solution: drop the existing database, detach the publisher, copy the 16.5 GB file across the network, reattach both databases, and then rebuild the extra, non-replicated, tables from scripts (which I thankfully kept).

The unfortunate side effect of this is that all three members of my team, including myself, lost the ability to work for about 8 hours and I had to troubleshoot the queries I had been working to prove to myself and my manager that the queries were right but the data was wrong.

The moral of the story: I’m not sure if there is one apart from ‘Seriously, why didn’t you check everything 7 times like you just said we should always do?’

I’m supposed to pass this on to other DBAs, so I’m going to tag Rick Kierner. He might not be a DBA, but I know he’s broken a few things in his day.

Some other participants in this quiz bowl of DBA embarrassment include Tom LaRock and Jason Massie.