July 10, 2009 at 8:28 am
Comments posted to this topic are about the item The Worst Advice
July 10, 2009 at 8:48 am
There we go! I was wondering what happened to the forum for this one!
Not a piece of advice, per se, more of a vendor problem. When I started here, half the production databases were set to auto-close/shrink, most with simple recovery model. Apparently those were vendor defaults.
Definitely agree about the reindex/shrink.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 10, 2009 at 8:49 am
Worst advice ever: "It can't be done."
It may not be pretty when I got done with it, but I can't think of one thing that someone said that about that I was not able to accomplish.
July 10, 2009 at 8:49 am
Some of the worst advice I've seen concerns moving databases and files. All of the articles mention detach and attach, but they fail to talk about security. I have found that when you detach a DB, any login which has that DB as its default gets changed to use Master as the default. Re-attaching the DB does not fix the problem. You must go in manually and change the login default back to the original DB.
July 10, 2009 at 8:55 am
The one I see that I dislike the most is "truncate the log file and then shrink it". That's the right advice in a very, very small number of circumstances, but only if it's followed by "back up the database before and after you do so", or something comparable. It's the wrong advice in most situations that it is suggested for.
Then there's the advice the index tuning tools provide. If followed exactly, you have a mess on your hands.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 10, 2009 at 9:05 am
Two that irritate me to no end and that I see far too often are running checkDB with a repair option and deleting the transaction log.
CheckDB with the REPAIR_ALLOW_DATA_LOSS option should be a last resort. It should not be there first thing advised or the first thing done. There are times when it is the appropriate course of action and there are times where it is not, but it should never be done as a knee-jerk reaction or as the default action.
http://www.sqlservercentral.com/articles/65804/
http://sqlinthewild.co.za/index.php/2009/06/03/does-repair_allow_data_loss-cause-data-loss/
Deleting the transaction log because it fills up. Seriously now.
It's not an error log. It's not an optional piece of the database. The transaction log is essential to database integrity and consistency. To make matters worse, people who suggest it don't mention that it's possible (especially if the transaction log is full) that SQL will not be able to recreate the log. The few that do don't mention that forcing SQL to recreate the log (emergency mode repair) can result in data loss
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2009 at 9:32 am
GilaMonster (7/10/2009)
Deleting the transaction log because it fills up. Seriously now.It's not an error log. It's not an optional piece of the database. The transaction log is essential to database integrity and consistency. To make matters worse, people who suggest it don't mention that it's possible (especially if the transaction log is full) that SQL will not be able to recreate the log. The few that do don't mention that forcing SQL to recreate the log (emergency mode repair) can result in data loss
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
I think this one is a remnant of being able to delete the locking file in MS Access back in the day when MS was not cleaning up it's orphaned files very well (not that they are much better now). One of the early steps in Access db troubleshooting was to make sure the .ldb was deleted. And since SQL uses .mdf and .ldf it must be the same thing, right?!?!
Oh well.
Thanks for the post Steve, this should turn into a good read.
July 10, 2009 at 9:48 am
"Put tempdb on its own drive."
The is certainly not bad advice per se. But, applying this rule cart blanc is not good either. I've seen smaller shops deploy tempdb on its own drive at the sacrifice of properly configuring disks for the OS, data and log files. Oh, and partitioning the same physical drive into two separate drive letters doesn't count.
July 10, 2009 at 10:23 am
Bad Advice
Yes, I agree that most of the time people have good intentions and don't intentionally give bad advice. It harkens back to a favorite quote of mine -
"It's not what you don't know that gets you in trouble. It's what you know that just ain't so". -Mark Twain.
July 10, 2009 at 11:17 am
Wayne West (7/10/2009)
Not a piece of advice, per se, more of a vendor problem. When I started here, half the production databases were set to auto-close/shrink, most with simple recovery model. Apparently those were vendor defaults.
... and vendors/developers that seem to think that SA is an acceptable login to use for their applications!
Regards;
Greg
July 10, 2009 at 12:10 pm
We had one product that insisted on using the SA account for installation (administration account was not usable because their complex installation program was hardcoded to use SA with whatever password you supplied).
jay
...
-- FORTRAN manual for Xerox Computers --
July 10, 2009 at 12:10 pm
Advising people to always use a NOLOCK hint to prevent blocking, with no explanation of the potential problems or even an understanding of the problems.
It’s a real sore point with me because I went on a job interview once where they didn’t hire me because I didn’t know that you should always use NOLOCK.
I hate that bad advice gets out into the world and turned into a standard practice because someone on a forum like this told people to do it that way.
July 10, 2009 at 12:13 pm
Not to be critical of the more skilled folks in this forum but even the most thought out and heavily reviewed & approved advice could still prove to be bad advice based on the situation (Environment & Case/Scenario) of the person asking the question.
For example in our environment we use a rather large SQL DB (over 100GB) that is full of what would be considered bad practice/design by most on these boards but because they are the vendors defaults or standard we are not always able to change these to me "Best Practices". And therefore it's possible for solidly good advice to be bad advice for us because of our situation.
There is no fool-proof solid advice or answer that will be right for every use in every scenario.
There also is no fool proof method for getting from posters all the info anyone would need to give that poster the best advice. There are too many variables which only continue to expand.
The best you can do when offering advice is to:
A) Be respectful and polite - The user may be less skilled then you (and feel like you are talking over their head) or more skilled then you (feel like your talking down to them) and so you asnwer with as little emotion as you can and as detailed as you can
B) List Pros & COns - If an answer has some ups & some downs to it then be sure to mention those in your answer
C) Do not be Offended - b/c not every one is running under the same conditions there may be times when your normally perfect advice does not work for the user so don't take it poersonal or be offended if they can't use your advice or go with something that sounds illogical to you.
Short of that we all have to take everything with a grain of salt; Trust But Verify!
Kindest Regards,
Just say No to Facebook!July 10, 2009 at 12:21 pm
YSLGuru: I completely agree. All advice is circumstantial.
I think what Steve is looking for here is advice that is common, but which is more often bad than not.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 10, 2009 at 12:58 pm
"Copy & paste this script into Query Analyzer and press F5"
....lol...and watch the games begin!
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply