Some of my greatest mistakes

  • bpwilso

    SSC-Addicted

    Points: 442

    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: 2571

    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: 27488

    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
    ;

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

  • MMartin1

    One Orange Chip

    Points: 27488

    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.

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

  • david.wright-948385

    SSCarpal Tunnel

    Points: 4028

    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;

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

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