T-SQL Tuesday #92, Lessons learned the hard way

This month's T-SQL Tuesday is hosted by Raul Gonzalez, he proposed the following:  For this month, I want you peers to write about those important lessons that you learned the hard way, for instance something you did and put your systems down or maybe something you didn’t do and took your systems down. It can be also a bad decision you or someone else took back in the day and you’re still paying for it…

There are so many things to share here so everybody can learn from each others mistakes, because all of us were once a beginner and no one is born with any knowledge about SQL Server.

Please do not be ashamed of sharing your experiences, you can anonymize the whole story if you want but remember all people make mistakes, the important is to learn from them and try not to repeat them in the future.


Before we start, do you ever wonder why in job postings they ask for a minimum of n years of experience? Well what is experience but the accumulation of mistakes you have made over time. Once you drop a table, I guarantee you, you won't do it again  :-)  So when they ask for n years of experience what they are saying is.... hey I know you messed up, and I know you did it somewhere else and not here, we are glad you did it somewhere else,  but I think you won't make those mistakes again, so we are willing to hire you now.....



So here are my stories

Script out proc with drop dependent object

I still remember this one,  even though it has been over 15 years ago. In Enterprise Manager/ Query Analyzer on SQL Server 7, when scripting out a proc there was an option you could check, this was the drop dependent objects option. Somehow that was checked and the table used in the proc was also dropped
I don't have Query Analyzer installed anywhere but even in SSMS there is an option to script the dependent objects...luckily the default is false..



So I got a nice call from one of the New York City agencies that data wasn't there. I was just about to start my lunch. I lost my appetite real quick. SO what I did was take a full backup and then did a restore with stop at to 2 minutes before I dropped the table. This worked and all the data was there. I however lost my appetite and did not eat my lunch that day. But I can tell you that I have never dropped a table again.




Script out table to make history table

This is one of my favorite lessons, lucky for me a co-worker managed to do this
We needed a history table, this table would be populated each month with the current data, the main table then would be cleaned and repopulated via a DTS package. So what do you do? You script out the current table, change the table name and run the script right? Seems easy enough.....

So this was done....  an hour or 2 passes and we get a call that the original table doesn't exist.

What happened?  Well the script had a drop table and a create table part, the table name change was only done in the create part...ooops.... not good



Staging server, test server..what's that?

I worked in at least 2 companies that did not have any test/staging or QA environment. need a change..right click DTS package right on production, make changes and save.... how productive people were.....  I am still amazed that nobody did any real bad things... But I believe we did use source control, but it was Visual SourceSafe 6.0




Start transaction... walk away casually

One time I had a query window open in SSMS and there was an insert or update statement and nothing else.  hit F5 and casually walked away. After a couple of minutes some people told me things started to take a long time and timing out.  Alright I executed sp_who2 and saw a SID in the BlkBy column in many rows. Ready to go find the person and give this person a nice lecture, I noticed that the SPID was mine?  WTF, I looked at my query window..nothing. I then scrolled up and as you can guess by now there was a nice BEGIN TRANSACTION statement at the first line....  Argh... so I commited the thing and that was the end of that. At least I didn't go home for the day yet.




Insert SELECT after DELETE and before WHERE clause

Sometimes, you have code like this

DELETE Sometable
WHERE a= b

you want to see what that will affect, so you do this and run the bottom 2 lines

DELETE Sometable
SELECT * FROM Sometable
WHERE a= b

Of course if you leave it like that, for example in a SQL Agent job, you will learn the hard way that all your data is gone, not just the stuff that the WHERE clause applies to


Use SQL to bring down other things in the company
The request was that support wanted to know immediately when an order was placed. No problem, we can add a trigger that populates a table and then we have a job that runs once a minute to email the support group. Sounds reasonable right? Someone decided to do a little testing.... this person created 100,000 entries.... I guess you can imagine what happens next. Yep, the email server was overloaded. We had to quickly delete these rows and truncate the table that was used to send the emails.


Change the user without telling anyone
Another one brought to you by a co-worker. This person needed some stored procedures to be deployed to production. No problem, put in ticket and we will get it done. So stored procedures were deployed and the front end code was getting some errors. It turns out that this person decided to create a new user and use that instead of the user used in all the other connection strings. At least this was easy to fix

I will leave you with this horror story from the microsoft.public.sqlserver newsgroup, I tried to find this thread but I was unable to find it.

Some person tried to do a backup but this person got confused and somehow managed to do a restore from the only backup they had, this backup was 1 year old.  The advice this person got? Update resume.

Not good, not good at all