The DBA Whoops
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/thedbawhoops.asp
-
Whoops indeed. Aren't you glad you didn't wipe out the post table
.Nice article.
-
Thanks and I am glad. That one wouldn't have been quite as fun to fix
-
why not?? it's only 190k rows... and the heart of the site
. -
Sometime ago I created a Query Analyzer template like this:
BEGIN TRAN
/* queries go here */
-- ROLLBACK TRAN
-- COMMIT TRAN
--
-- SELECT @@TRANCOUNT
-- DBCC OPENTRAN
If I make any ad hoc changes, I always use it.
-
There must have been something in the air yesterday, I did exactly the same thing! Working quickly under pressure on a production system I ran an update but..... didn't highlight the last line of the where clause.....849 lines updated instead of 2...aaargh! Fortunately I preceded the update with a 'begin tran' so I rolled back and tried again!
I just reckon this kind of human error is inevitable. Steps to minimise it are (i) implement good change control procedures and adhere to them (what I did, needless to say, contravened all my employer's procedures which made it even worse for me) (ii) get into the habit of preceding all DML statements with 'begin tran' or (iii) set implicit transactions on your session or at a Server level or (iv) use the Enterprise Manager grid display to update individual attributes.
Option (ii) - get into the habit of preceding DMLs with 'begin tran' - is the easiest and most hassle free IMHO.
-
Been there, done that.
To be honest, when I have a single record update I open up the table in Enterprise Manager and edit the record manually. You can't accidentally update umpteen rows accidentally!
For changes to a live environment I tend to script absolutely everything and test on a development database. I run the script from the command prompt. Not perfect but there you go.
I like the idea of the COMMIT / ROLLBACK template.
-
question? Why don't you have a test platform where changes are made and tested then the changes are uploaded to your production system through reliable change management? It means this sort of thing very hard to happen as the mistake is most often picked up before it makes it into production. The size of your database you could fit a test one on your lap-top. The table you talk about could be termed static data which you script out the data into insert statements and add to your source control where changes could be made in a very controlled manner so rolling back would be very simple and totally auditable. Why don't you use our software to do all of this for you? It is after all free to you (Steve Jones)...
regards,
Mark Baekdal
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server
-
Mate,
I really feel for you, but at least every DBA has done this sometime or the other. I have learn't a lesson from this and I ensure that every thing that I could possibly wrapper in a transaction, I will...
This really helps...
-
Great article - think a lot of us have been there. Now if only Ctrl Z would work in Query Analyzer...
Andy
-
Great article! And a situation which I think most DBA's have had experience with, even if it just involves messing up a test database and inconviencing a handfull of people for an hour or two...
My avoidance technique is a variation on the begin/commit:
Select from table_a where xyz
Begin trans
update table_a where xyz
Select from table_a where xyz
rollback trans
--commit trans
This gives me the ability to check the data after its been updated before i commit it and without having to lock any rows and or tables.
Now if only that worked on girlfriends...
-
Now if only that worked on girlfriends.
The problem with COMMIT TRANS in this case is that it results in a wife and massive consumption of resources.
ROLLBACK TRANS and your handling better be ready to cope with IO Exceptions.
-
I remember doing this once before, and joking that there should be a 'WHEREVER' (pronounced with a rebellious female teenager accent) clause added to the language, and that or a normal where clause should be mandatory. Joking aside, I'm not sure it isn't a bad idea, at least for UPDATE/DELETE operations. I've done it myself, probably more than once, and seen others do it many times.
Last time I did it, by the way, was on a table with 200,000+ rows, with my boss standing behind me (I blame him for hovering over my shoulder at the time). Got it back without much hassle, but it was amusing watching the colour drain from my boss's face when we realised what had happened
Kev
-
Boy does this hit home. I have made similar "feature fixes", and that is when I find out if the deodorant was worth the dollar ten or not.
I have seen someone give a demo on how easy it was to recover a database after you delete it. They thought they were connected to the test database. Need I say more? We are all human (with a few questionable exceptions) and mistakes happen, so yes keep your cool and work the problem, don’t let the problem work you.
Stacey W. A. Gregerson
Viewing 15 posts - 1 through 15 (of 62 total)
You must be logged in to reply to this topic. Login to reply