SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Some of my greatest mistakes


Some of my greatest mistakes

Author
Message
Dave Poole
Dave Poole
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26682 Visits: 3544
Comments posted to this topic are about the item Some of my greatest mistakes

LinkedIn Profile
www.simple-talk.com
RandomEvent
RandomEvent
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1867 Visits: 528
Great article, my experience is you learn for more and far more quickly in a crisis than at any other time. Amazing how stress and the constant management badgering lead to an upping of the game.

I've made so many different mistakes over the years, some of them real clangers that I can't really pinpoint a greatest. I'm sure there will be others to come yet maybe one of those will get the title.
david.wright-948385
david.wright-948385
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2052 Visits: 994
One of my devs had exactly this problem a few weeks back. My advice was:
* always alias tables
* always prefix column name references with the table alias
* it's a lot clearer to join tables using a JOIN clause;
* don't use "where <id> in ( <subselect> )" unless there's a compelling reason to do so.
Bill Talada
Bill Talada
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5145 Visits: 2232
My biggest mistake was 10 years ago when I emailed an employee about 46,000 alerts due to an infinite loop.


-- infinite loop example
DECLARE @t table (tkey int not null);
INSERT into @t values (1),(2),(3);

DECLARE @i int;

SELECT @i = tkey FROM @t;

WHILE @i IS NOT NULL
BEGIN
PRINT @i;

DELETE FROM @t where tkey = @i;

-- @i is not set if there are no rows in @t
SELECT @i = tkey from @t;

-- that was an infinite loop, should have used the following line instead
-- SET @i = (SELECT TOP(1) tkey from @t);
END

john.wb
john.wb
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 205
Another good practice is to wrap any data affecting statements in a transaction but comment out the COMMIT TRAN. You can see how many rows are affected and issue a rollback if the number looks wrong.
parrish.guido
parrish.guido
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 68
Thanks for sharing. I always enjoy learning from these types of experiences. One thing I always do no matter how trivial of a script I run against a production database is to wrap the entire script in a transaction as follows:

BEGIN TRAN
DELETE FROM table WHERE id = 5
ROLLBACK


This allows me to see how many rows are affected while performing a safe operation. If I see "1,000,000 rows affected." when I'm expecting 1 row, then obviously something is wrong and I probably just blew out my log file. If it does work as expected, then I simply swap out the ROLLBACK for a COMMIT or exclude the BEGIN TRAN and ROLLBACK lines from the batch which will auto-commit. This has saved me from many unexpected and potentially epic fails.

Please keep in mind that this won't completely ROLLBACK all changes in all scenarios but in general, it's a good practice.

KenpoDBA
KenpoDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4482 Visits: 656
I love pieces like this. I think DBAs need to know that experts make big mistakes too. We have a webshow, DBAs@Midnight, and we used to go to PASS and interview some of the biggest names. One of the questions we always asked everyone was "What's the biggest mistake you've ever made in production?". We wanted to regular DBAs to hear that even the biggest of the big have made mistakes. BTW, my favorite answer came from Itzik.
These pieces are always a delight. If you pin me down sometime at a conf, I'll tell you how I brought down a cluster for 80 sites.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:


Nelson Petersen
Nelson Petersen
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 440
"One of the questions we always asked everyone was "What's the biggest mistake you've ever made in production?""

>>>> This is my favourite question to ask interviewees. I mention an example mistake from my past as part of the question.
Denial that you have made any mistakes in production most likely means you: 1) have extremely limited production experience,
2) have very small task lists with no deadlines, [this is a variation on #1] or 3) are completely unacquainted with truth and cannot face reality.
How we take ownership of and respond to our mistakes is a test of character as well as technical skill.
Ideally, we admit our mistake(s), have some documentation of what we did, and have some idea on how to repair the damage.
Depending on the situation, we might need to create a backup of the disaster, before attempting corrective action.
Also, depending on the impact and scope of the mistake, communication with multiple levels of management and the business might be required.
At a minimum, tell your manager and your immediate team. Hopefully your company culture has the wherewithal to effectively deal with mistakes.
(Firing is not effective. Mistakes, by definition, are not done with malice aforethought.)
For extremely small mistakes, you might be able to fix it before the whole company knows about it, but no guarantee.
The ripple effect can be amplified into a tsunami.

Proceed with caution!!! Assumptions are required, but one or more of them are what led to the mistake in the first place.
Verbalize, document and acknowledge your assumptions.
You might find that you're assuming your last step was the only mistake, when the larger mistake occurred three or nine steps earlier.
Stop. Assess. Communicate. Discuss. Create a restore point if possible. Assess. Take corrective action.
If this post sounds like I have made mistakes, then it accurately reflects reality. I have had a little success in not repeating some of the more painful mistakes, though.

The humility of admitting it is possible for us to make mistakes, regardless of our experience and knowledge, is a virtue to be practised every day.
Michael L John
Michael L John
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9530 Visits: 8531
john.wb - Thursday, May 18, 2017 6:44 AM
Another good practice is to wrap any data affecting statements in a transaction but comment out the COMMIT TRAN. You can see how many rows are affected and issue a rollback if the number looks wrong.

Not sure I agree that this is a good practice.
This was the "good practice" at a previous place I worked. This caused a significant number of deadlocks while the users looked over the results of the query before doing the commit.


Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
david.wright-948385
david.wright-948385
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2052 Visits: 994
Michael L John - Thursday, May 18, 2017 12:09 PM
john.wb - Thursday, May 18, 2017 6:44 AM
Another good practice is to wrap any data affecting statements in a transaction but comment out the COMMIT TRAN. You can see how many rows are affected and issue a rollback if the number looks wrong.

Not sure I agree that this is a good practice.
This was the "good practice" at a previous place I worked. This caused a significant number of deadlocks while the users looked over the results of the query before doing the commit.

Ad hoc queries on a live server aren't an amazingly good idea, especially untested ones.
Better would be to test your script on the dev instance first with an approach of "do it", "check it", "roll back". Then check again with rollback on the live instance just in case something has changed, then run on live with a commit when you're absolutely happy.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search