We all fear of making the big mistakes. Sometimes we accidently truncate the wrong table, update the wrong customers or make other big mistakes. Often, backups or copies of the database comes to rescue, but sometimes backups are to no avail.
Like when a workmate of mine missed a column in a join and x number of thousand customers got two packages with twice the number of products instead of what they should have got. Happy day for customers and post office, not so happy at the office :-)
What was your big mistake? And what practices do you have to prevent them from happening again?
asked Feb 23 '10 at 08:17 AM in Default
Running some T-SQL in SSMS against the wrong server (live vs test).
answered Feb 23 '10 at 08:27 AM
Kev Riley ♦♦
I can't really think of a time that I made a massive mistake with a database (touch wood), but I have had to recover from some classics from my colleagues.
The best was when a guy truncated a live transaction table instead of the data loading table that he meant to truncate... The look on his face was a picture worth a million pounds! Shortly after that was when I added the 'big yellow test mode' to my editor (does the BEGIN / ROLLBACK mentioned by Kev).
The next best was when an operations guy removed a disk shelf from a rack while it was still on so he could read the serial number from a sticker on the top. The cable wasn't screwed in properly, and the whole database got marked as suspect. Cue a 70 hour week from me recovering over 6 billion rows of data!
answered Feb 23 '10 at 08:43 AM
Matt Whitfield ♦♦
I restored a development database on top of production when I wasn't paying attention to which server I was logged into. That was a long time ago, but it was a doozy.
answered Feb 23 '10 at 10:03 AM
Grant Fritchey ♦♦
We use an app to concatenate all the scripts for a system release into one master script. A few years ago I ran a release containing about 175 scripts on the test server. Or so I thought. Within seconds all hell broke loose. Too many windows open - it was the live server. We were down for about 2 hours while I untangled that one.
To stop it happening ever again, I changed the SQL the app appends at the start of the master script to check @@servername and raise a severity 20 error if it matches the live server's name. I actually have to comment it out to get the script to run on release day. Every now and then I forget and then the result is "A severe error occurred on the current command. SQL Server is terminating this process." After my heart restarts I get a nice warm glow.
The weird thing is, I've never run it by accident on the live server again but I live in hope!
answered Feb 23 '10 at 11:09 AM
This was actually a mistake I had to correct a few years ago.
I was part of a development team for a hospital database. We realized we were receiving bad data when we noticed that a certain patient had met with over 100 doctors during a single encounter (visit), and the number kept growing slowly. Approximately 0.1% of our doctor contacts were being associated with this same visit. It took me over a week of pouring through classic ASP and Oracle SQL code (with joins in the WHERE clause) to find this faulty statement in a stored procedure that was only executed for some drop-in visits (i.e. without an appointment). This isn't the exact statement, but it's pretty close:
This procedure would return hundreds of thousands of rows, but the UI would just take the top 1. Of course, that top 1 was always the same record.
Unfortunately, since this affected "only" about 1% of the data, and we estimated the problem started about 3 months before anyone detected it, we had no way of correcting the bad data.
answered Feb 23 '10 at 02:59 PM