The DBA Whoops
-
I have no idea.
I only know that it happened during adding a drive to the server.
-
Done this one or two or -maybe- three times in the past
I think you can enable implicit transactions by default in QA -- then you'd only have to issue a rollback if this happened. No need to remember to actually start the transaction. But then again, you wouldn't want that for EVERY connection.
A cool feature request might be connection-specific options for SSMS -- If you're connected to server X, use implicit transactions; otherwise, don't -- X is your production server in this equation.
--
Adam Machanic
whoisactive -
It might be upsetting, losing some data, and otherwise, and it might seem like rebuilding the sandcastle, one grain of sand at a time.
But everything gets washed out to sea one day.
I do practice having a couple backup alternative databases around should bad things happen via good intentioned update statements.
A few ones and zeros might be lost and I still find myself a little choked up when formatting ancient 3.5 floppies, but sometimes you just have to let go.
Have a great weekend everyone.
-
Francisco's post reminds me of a time recently when I was helping my dad with a carpentry job. I got in a hurry and cut an expensive piece of wood...about 2 inches too short. There is certainly no Ctrl-Z for handsaws.
He just quipped with his wry sense of humor, "Well son, it's 2005 and they still don't make welding rods for wood." As embarrased as I was at my mistake, I still laugh looking back at that.
Here's to all of our systems being protected and backed up adequately enough that we can look back on our mistakes and see the humor in them. Thanks to everyone that's shared their expriences here. Enjoy your weekend everyone.
My hovercraft is full of eels.
-
In this era of Sarbanes-Oxley, coming clean (informing management) is huge since the spirit of SOX is not sweeping things under the covers but making deficiencies known as well as the plan to address them.
No one likes this sort of event but when the recovery script is pulled off of the shelf, tweaked if needed, runs flawlessly, and business returns to normal it does make a strong point to management as to why a DBA is on the payroll.
-
I have done something that before.
Then I changed my practice.
for the SQL statement with where cluase,
I always do this,
update XXX set xxx = XXX Where -- must be one line with keyword
<where clause>
Although I may still highlight a row, but it will be incorrect syntax,
rather than update all.
-
That's a neat trick... just have to rethink the way I format my statements now
. -
How did you restore one table ?? Not possible in SQL without filegroups as far as I know..
-
I love articles like this! Exposing the human factor in all DBAs. I too have done things like this and have learned from this as well as my own OSMs (Oh-$h!t Moments). Slowing things down definately helps in high crisis level situations.
Thanks for the great editorials too - I look forward to the newsletter everyday!
- Jeff
-
Hi Steve,
Great editorial, and some sound advice (both in the editorial and in this thread).
Your error reminded me of an error my former boss made many years ago (back in the dark age of SQL Server 4.2). He had to remove a bunch of rows that had been erronously inserted in the DB. There were to many to remove one by one, so he wrote a complicated WHERE clause to find the rows to delete. Since it was a complicated issue and the table was critical for the customer, he made sure that he got it exactly right before actually changing anything, by using this template:
-- DELETE VeryImportantBigTable
SELECT some columns
FROM VeryImportantBigTable
WHERE something complicated
AND something even more complicated
.....
AND yet more conditions;
When he had finally managed to tweak the WHERE to return the exact rows that needed to be removed, he uncommented the DELETE line and executed the query. Yeah, that's right - he forgot to remove or uncomment the SELECT and FROM lines first.....
Luckily, the table was VERY large and there were some VERY inefficient triggers on it. When, after a minute or ten, my boss started to ask why his query took so long, and at the same time end users started calling us to complain that the system had become unresponsive, my collegue glanced at my boss's screen, quickly assessed the situation, opened another connection in his isql/w session and killed my boss's conenction.
The rollback still took about half an hour.....
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
One thing I like to do when feeling especially nervous about updates to a production table is run a SELECT with the WHERE clause I will use in my update or delete. I check the rowcount for the select, and use that in my update within a transaction so that I can automatically rollback if the count differs...
--Step 1 - get count of rows affected
SELECT * FROM testing
WHERE fieldTwo = X
(1 row(s) affected)
--Step 2 - run it
BEGIN TRAN
UPDATE myTable
SET fieldOne = 'abc'
WHERE fieldTwo = X
IF @@Rowcount <> 1
ROLLBACK TRAN
ELSE
COMMIT TRAN
-Dan B
-
I had a similar event last week. Unfortunately it didn't end so well.
The critical part in your damage control method is backups, and I found out the hard way how important it is to verify backups.
In our company the network admin does many of the dba functions, including maintenance plans. He replaced some drives and tape backup systems about a year ago, and apparently the nightly tape backup was addressing a drive that no longer existed. When I went to restore the table I got 'device cannot be found'. Well, there had been no backups for OVER A YEAR!!!! Luckily this was a non-critical table, so the loss of data was not too painful, but we were very lucky to discover the problem without having lost something important.
Have, test and verify disaster recovery plans. Without that all else is pointless.
-
Nice idea, skrilla!!
This isn't something I would recommend trying out, but in a similar situation in a merge replication environment, I was able to stop the merge agent quick enough to prevent the changes from being replicated to one machine. I was then able to select the good data into a temp table to use to update the listings back to their original values.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Hi Steve,
I feel your pain. You offer some great advice, your counsel of 'don't panic, and make a plan' is probably the most useful. No point of compounding the problem. But I would add two things to the end of the punch list, 1) Confirm full recovery and 2) Document the plan to share with support staff.
Over the last 26 years of database programming I've taken pressure, exhaustion, or overload and manufactured several trashed databases. Now I go in looking at what to do if..., plan a recovery strategies, and then train and mentor the 'less scarred'.
In our day the very most important thing many companies have is the data - we have the responsiblity to guard it with all diligence. And part of that diligence is preparing for the (hopefully very) rare whoops.
Someone once said that we (should) learn more from our mistakes then our successes. I should be a wizard by now, where's my hat?
Thanks for so many years of great writing and work,
Tom
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
-
Excellent article. I've been through similiar situations (haven't we all?) but I like the point about making a plan and sticking to it.
Viewing 15 posts - 46 through 60 (of 62 total)
You must be logged in to reply to this topic. Login to reply