Some of my greatest mistakes

Question

Some of my greatest mistakes

September 11, 2023 at 7:10 am

This was removed by the editor as SPAM

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

bpwilso SSC-Addicted Points: 448 More actions · Answer 1

I'll add to this that I have most definitely learned the transaction rollback can be a great tool to utilize. One more for making sure that you're actually targeting the database(s) and/or servers that you intend to.

I one time in under 5 seconds erased a central fact table for over 450 customers. The color and panic drained from me faster than I really remember. This was before transactions were something I could utilize and I had yet to learn to sometimes create duplicate tables in case I messed up the original beyond recovery. Triangle name changes and truncate were in my script and the utility I happened to be using was my own. It traversed different server groups and database groups based on options selected and would run whatever SQL I could want to against those groups. I had the check all and check none buttons really close together and with a combination of keyboard macros and haste, selected the check all and run options. There was no way I was able to recover. I did get the attention of the systems team to lock out everyone immediately, and told them what had happened. They asked me to leave for the day. It was hard leaving thinking that I wouldn't be back again. The phone calls and subsequent meetings that came from my mistake were some of the worst I ever had to endure. The only real positive was my approach to the situation and admission of guilt. I knew what had happened and what was needed to resolve it. I owed the systems team members a few gift cards and a round or two as a quiet gesture of thanks in forcing them to work magic to restore a single table in a system that was setup to do so.

Good tools came out of the experience. As did a strong lesson of humility. Always have a plan B and maybe C through G when it is possible.

Lonely Rogue SSCrazy Points: 2578 More actions · Answer 2

About the DELETE statement that uncovered bug when no aliases are used ( working of correlated subquery) - I would first execute SELECT in place of DELETE/UPDATE, ensure that output is per my expectations and then execute actual DELETE/UPDATE.

SELECT TabA.*

--DELETE 

FROM TabA JOIN TabB ON TabB.Id=TabA.Id?

This proved always life-saving.

--In 'thoughts'...
Lonely Rogue

MMartin1 One Orange Chip Points: 27768 More actions · Answer 3

You can use something like the below to indicate a total failure somewhere. At least in the testing phase.

begin transaction 

declare @rowcount int
Select @rowcount = count(*) from star_wars_film;

DELETE
FROM dbo.star_wars_film
WHERE film_id IN (
SELECT film_id
FROM dbo.purge_test
WHERE purge_type = 1
);
IF @@rowcount= @rowcount 
Rollback
ELSE
Commit
;

----------------------------------------------------

MMartin1 One Orange Chip Points: 27768 More actions · Answer 4

I know about the hesitation with open transactions, but hard to be too careful when it comes to ensuring the data manipulation is done correctly.

----------------------------------------------------

david.wright-948385 SSCarpal Tunnel Points: 4039 More actions · Answer 5

MMartin1 wrote:

You can use something like the below to indicate a total failure somewhere. At least in the testing phase.
DELETE
FROM dbo.star_wars_film
WHERE film_id IN (
SELECT film_id
FROM dbo.purge_test
WHERE purge_type = 1
);

That would be a whole lot better with table aliases and a join!

DELETE film
FROM dbo.star_wars_film AS film
JOIN dbo.purge_test     AS test ON test.film_id = film.film_id
       AND test.purge_type = 1;