Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBA Learning Experiences (Oops!)


DBA Learning Experiences (Oops!)

Author
Message
Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)

Group: General Forum Members
Points: 984 Visits: 82
I am Robert Marda and am hoping to spark some constructive conversation here. This is the first of a few topic ideas I have.

I have been working with SQL Server and SQL since March 1999. I was given my first DBA position in May 1999.

Within my first month as a DBA I successfully
deleted all rows in our main table - Oops!(containing the location and serial numbers of all computer components used to produce ID cards at US military bases). The deletion was not intentional but happened due to my lack of SQL knowledge and the fact that we had no development nor testing environment (a very bad combination). Thankfully I had run a full backup recently and was able to restore from that.

Because of this experience, I usually develop all queries as a SELECT to ensure I see only what I want to change before converting the SELECT to a DELETE or UPDATE statement. One of my philosophies is "What you see is what will be changed."

Robert Marda
SQL Programmer
bigdough.com



Robert W. Marda
SQL Programmer
Ipreo

Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)

Group: General Forum Members
Points: 984 Visits: 82
I forgot to post an invitation for others to add your own learning experiences here. Please add experiences you've had that taught you a valuable lesson about being a DBA (or SQL Programmer)

Robert Marda
SQL Programmer
bigdough.com



Robert W. Marda
SQL Programmer
Ipreo

K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6816 Visits: 1917
One of the things I can tack on to this is if I'm having to run queries manually to make changes to the data, I'll start a tran. After each step of the process, I'll verify that the data looks as it should. If it doesn't, I can roll everything back. Once I get to the end of the process, if everything is as it should be, I commit the tran.

It should be noted that in cases like this I've been having to make changes to data in production (don't ask), and at the time the app is down (usually because the data is hosed). As a result, I'm not worried about blocking others from the resources... got to get things fixed that the app broke!


K. Brian Kelley
bk@warpdrivedesign.org
http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@‌kbriankelley
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36146 Visits: 18751
I extensively use the CTRL-SHIFT-C and CTRL-SHIFT-R extensively in QA to comment and uncomment a series of lines when making queries (as well as begin tran).
Why?

Too often I've typed the following:
-------------

select *
from product
where producttype = 2

delete product
where producttype = 2

---------
Then I highlight the first query and run it. check my results, then highlight the second query and run it.

Of course, sometimes I highlight "delete product" without the where clause

you can imagine what happens then....

Since I may have a few updates/inserts/deletes, I decided to comment them out until I need them and I can just run the entire script without worrying about highlighting. Not the greatest, but it prevents some of my stupider mistakes from being repeated.

Other goofs:

-- Running and update on the wrong server (another reason to use begin tran) (quite a few times)

-- not verifying the results of a query before telling someone I've done it (more often than I would like)

-- ignoring a DBCC error (was busy). DB was down for a day while I worked with MS Support to fix it. (only once)

-- making a change on production without testing in the QA environment (not too many times, but still to many)


Steve Jones
steve@dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)

Group: General Forum Members
Points: 984 Visits: 82
One technique I use to try to avoid deletes or updates when highlighting only a portion of a query is to use table aliases and always include a FROM clause (even though not required). This way if I only highlight the top line it will fail because SQL Server can't find the aliased table.

For example:

delete tbl
FROM tablename tbl
WHERE UserID = 1

I am going to have to look into using begin tran. I'm sure this will help me avoid some of the mistakes I make.


Robert Marda



Robert W. Marda
SQL Programmer
Ipreo

Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36146 Visits: 18751
aliased tables is a great idea. Never use that one. I'll give it a try. Thanks!!

Steve Jones
steve@dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Lonnie Wimble
Lonnie Wimble
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 51
Here's a tip I find useful. Many times I will have several scripts in the same window of QA. To ensure that I don't accidently run a deletion that follows a select, etc., I precede all my delete and update actions with a comment -- tag. Hitting Alt-X accidently while in the script window could have disastrous affects :-)



Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36146 Visits: 18751
Lonnie, try highlingting and using CTRL-Shift-C to comment a series of lines. Works great to prevent that accidental delete!

Steve Jones
steve@dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
mchapin
mchapin
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
There are some good tips here.

One that has worked for me is to put a string of characters, I typically use ******, on the first line of the query analyzer window. If you accidently run the query without anything highlighted you will get an error instead of all the queries running.



Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)

Group: General Forum Members
Points: 984 Visits: 82
I have used a similar technique in DTS packages. When testing a certain part of a DTS package and unable to monitor its progress I'll go to where I want the package to stop and put a line of code in a task that will fail to force the package to stop at just the right spot. This way I don't have to worry that it completes and imports data when the data is not ready or a transformation is not working right (and this has happened to me a time or two).

Robert Marda



Robert W. Marda
SQL Programmer
Ipreo

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