Bonehead moves?
-
So anyone pull any bonehead moves on a production database lately?
Just recovered from the classic "missing where clause" meself -- was updating a column on a row via SQA, had a syntax error on an invalid column name so highlighted the update statement & only part of the column list whilst omitting the where clause and voila: 4200 rows zapped. Real time factory production environment, had to call the client & get them to suspend operations for 10 minutes while I gathered myself together and fixed the data.
Fortunately, we track the column's value redundantly due to legacy issues, so just needed to do an update via an inner join to the redundant table, but still had that 2 second gut-dropping realization that I'd just screwed up in the worst way (to put it politely).
Mondays suck.
Vik
-
your post reminded me of a similar one some time back...while all the bonehead movers are fighting to get in their posts first here's an empathetic post to make you feel not so alone...
**ASCII stupid question, get a stupid ANSI !!!** -
Yep, been there, done that. This morning.
Fortunately the client involved was pretty cool about it: "Geez, that's the most excitement we've had around here in weeks. And it wasn't even that exciting."
Thanks for the link.
Vik
-
I remember a long time ago, sitting next to a DBA 'guru', flown in specially from Frankfurt, who was setting up a view for a enterprise systems management system I was installing for a major international company. This system was part of the company's network operations centre, monitoring their entire EMEA network. They had 8 management consoles in a curved bank, linked to a projector so they could display the output of any console on the wall with glorious 3D representations of all their main systems. The system was designed to give a visible and audible warning in the event of a failure.
I watched as the guru typed in the view definition, put in the correct SELECTION criteria, but omitted the JOIN clause. I just had time to say 'Err...!" before he created a multi-million row cartesian product. The database went 'ooof', and everything ground to a halt. The display on the wall behind us suddenly started showing a cluster of red icons, and the Starship Enterprise red alert klaxon noise sounded out across the room. The ops staff started running around in a panic trying to find out what on earth had gone wrong...
I have always thought that German is a wonderful language for swearing in, you can put so much expression into your expletives...even under your breath.
David
If it ain't broke, don't fix it...
-
I was busily popping data in and out of temp tables to get to some conclusion and kept having to drop them so I could start again.
Someone popped their head around the corner and started talking to me while I was dropping a table, and I accidentally omitted the # from the table name and dropped the customers table from our production database. That was a bit hairy.
Now I never name my temp tables the same as production tables..
You live and learn.
-
Several years ago, I 'proved' to my boss that the asp frontend that I'd written for a stock options system wasn't vulnerable to sql injection.... and in the process I deleted the entire users table
Fortunatly it was our dev database, not a production one. Unfortunatly, I didn't have a backup. I spent the evening replacing the users table, record by record.
Another one, fortunatly not me. A collegue was writing something in vb using the SQLDMO library. The tool was suppose to generate a sql script to drop all foreign keys in a databse (I don't remember why). He generated the script and ran it without checking.......
The script didn't contain ALTER TABLE DROP CONSTRAINT commands, rather it contained DROP TABLE commands. He dropped every table in the entire database. He then went looking for the backup (there was supposed to be a backup taken every night) and realised that he's disabled the backup job over a month earlier. (there was no DBA, it was a dev database)
I believe it took most of the weekend before he got the database back together.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Besides the missing WHERE clause? On both updates and deletes?
Of course, I'm a production DBA. And I've had some good ones, fortunately with small databases.
- Restore QA over production by mistake. Same DB, wrong server. Luckily I had good log backups and could recover quickly, though not without people knowing.
- Send "test" emails to real clients because the addresses weren't changed when the restore occured on test db.
- Import the same file a few times while testing, therefore ensuring myself of a nice evening of data cleanup for duplicates.
and many more
-
Not with SQL Server (yet), but had a most embarrassing moment on Oracle.
We were having a database system built and I was supposed to be the DBA (I had programming training and had been hired as a programmer). The database kept crashing because a couple of files would grow too large and the vendor, in their infinite wisdom, put them in a folder that wasn't large enough. Again, I'm a newbie at this point. They tell me to delete the files when they get too big. Okay, no problem there. But one day I figure that I will take a look at some of the other files in this folder and see if any of them can be removed. I find a file called d_database.sh. I'm not a UNIX person, and I click twice on the file to open it up. That's what I do in Windows. Ooooppsss... dot sh means it's a SHELL file and is the same as .exe in Windows. The file runs. Guess what the d_ means.....delete. I start seeing delete messages run across my screen. Then it finishes. My first thought is ROLLBACK. It's a great command in Oracle and will rollback all the deletes. I issue the rollback and get ROLLBACK COMPLETED. Sigh of relief. Then I run a script and the tables can't be found. What the heck? The rollback completed so where are they? Turns out there were several COMMITs in the script and nothing rolled back. Wonderful. Next thought - nobody saw me at the computer, I can walk away and pretend it didn't happen. Then I go to my supervisor and admit what happened.
Best parts (for me) were:
1. the system had not been accepted yet and was still the vendors responsibility.
2. this was one of three identical systems (two other on-line databases were going to be moved to other locations once it was accepted)
3. as part of the acceptance the vendor was to show us how to recover from a loss of one system.
So, my bosses looked at it as a training scenerio. How would the data be recovered from one of the other systems? Turns out the vendor didn't know how to do it, they could only recover 2/3rds of the data. Along with other issues, they lost the contract and we went to a different vendor using SQL Server and that's also the story of how I became a SQL Server DBA.
-SQLBill
-
Like everyone else I have pulled the forgotten WHERE clause when highlighting rows so I adopted having BEGIN TRAN in all to be sure I can rollback or commit if an error.
Did however once delete a database completely (even to the file) but was able to recover with an undelete utility, fortunately was a dev database it was just the work I was concerned with losing.
Also pulled the infamous drop a table before thinking, however adopted a long time ago the rule of making a backup of the database before any Delete/Insert/Update/Drop I run.
Did however delete a single row in a table once which I had to recover (with a utility) from the transaction log (inserted after backup and I had yet to forgot to highlight the BEGIN TRAN as well).
Recently thou had scripted out an entire process and made all notes as to the implementation plan for a DBA on another server which require removing replication from the database before the script was ran. He unfortunately just disabled replication and the whole thing failed in bits. Fortunately had him make a backup right before implementation. Unfortunately he resotred and did the same thing so we had to restore and go again. On top of that because testing showed no issues (as long as the plan was followed) I forgot to have him stop email notifications, 3000+ emails later I stopped it before the last try. Now I will begin scripting removing replication and creating it after the script.
Sure I will have more in the future with new rules to impose on doing the job right without issue.
-
I am taking the Fifth.
Regards,Yelena Varsha
-
Back when I was just learning administration I had a unix admin looking over my shoulder one weekend while we were doing an upgrade. It turned out that we needed additional space on the root drive to upgrade the OS and apply some service packs. Since we had recently upgraded SQL Server from 6.5 to 7.0 on this box, there were some old files left over from 6.5 install and the vendor suggested we simply uninstall them. The only problem was that during the uninstall, when you get to the point where it asks you "Do you also want to remove the SQL Server data files?" the other admin just clicked "Yes" right about the time I was trying to say "I don't think we should....oh @#$%!".
Thankfully, we were not using the default folder structure created by the install so as it turns out, only the log files were deleted and we were able to recover the data and create new log files. But it was a hell of a scare and ever since then, my upgrade plans have always included some extra safety nets to avoid this kind of stuff.
I've also gotten into the habit of having different passwords on production and development servers so I don't run updates in the wrong place as that's happened before too, but that's another story...
My hovercraft is full of eels.
-
Schema upgrades ... losing data in production of course ... two words ... ErWin ... ErOops !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I remember (and try to forget) the time I did not preallocate space to the database and database log. My log ran out of space and caused some red face!
Curtis
- I just forgot my first WHERE clause today! I was helping a co-worker fix a problem that a customer had complained about. I correctly identified the problem (yay me!) and wrote him a statement to test out on the database. Fortunately, he had gotten a copy and was testing it locally. "Emma, why did your script just tell me that 127 thousand rows were affected?""um...."Does this mean I'm on way to being a full-fledged DBA?em
-
Yup
Ran a DBCC SHOWCONTIG () WITH TABLERESULTS, ALL_INDEXES on a 180m row table. It has about 9 indexes. Ran for about 30 minutes.
I assumed I was running it on our test box so I kicked it off and dissapeared to grab some lunch...Until of course I was called..
Think I need to book an appointment at the opticians and get my contact lenses strengthened!!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply