5. Turning on auto-shrink
It might make disk space alerts go away for a minute, but it can cause lots of problems.
4. Enabling xp_cmdshell and giving the SQL Server account administrative rights in Windows
Young DBAs often do this to try to get a quick fix in place to manage backup files. Later on, they realize they can manage all those things in PowerShell or cmd SQL Agent job steps and give more limited permissions. But now they’re not 100% sure of what will break they disable the option and they live in fear of a security audit.
3. Running transaction log backups every 30 minutes, only during the day
“How often should we run these backups?” (Does internet search) “30 minutes seems popular.”
Your backup frequency should be driven by RPO and RTO. And don’t turn them off at night– usually people can still make changes in the system, and there’s no reason to have a giant log backup in the morning.
2. Looking for one magical feature that will fix all performance problems
When you first start out, it seems like all those expensive enterprise features must make everything blazing fast. Later on, you realize that humble things like indexes, memory, and carefully written TSQL are worth all the effort.
1. Being Rude to All the Developers
As a beginner DBA, it’s easy to work long hours, get stressed out, and start to feel like you’re the only one protecting the SQL Server. You start to say “no” a lot. And sometimes you lose your respect for other people.
But you can’t let that keep happening: if you go down that road, it’ll ruin your job. You’ve got to regularly step back, see things from other people’s perspective, and be part of the team.
19 Comments. Leave new
I remember walking in to my previous job. Day 1: I found pretty much every database was set to auto-shrink/auto-close. Poor backups, no DBCCs, etc. It took a bit of time to get everything cleaned up to a decent functioning level. Fortunately all of the DBCCs were clean.
#6. Eschew stagnation.
Practice with new, unfamiliar features. Practice full, differential and xlog restores.
I’ve never heard of turning off log backups at night. Do you know why someone would do that?
I would typically reduce the timing of backups after hours. For example, I might do log backups every 10 minutes during office hours but only every half hour or hour from 7pm to 6am. Adjust seasonally for end of month or end of year processing.
But that was then, now I don’t see the benefit. Just do ’em 24/7 every 10 minutes, the overhead space for a t-log backup with no records is purely minimal. It could also help detect anomalous activity: if your 3am log backup is > 0 and you’re an 8-5 operation and no maintenance is running at that time, then something needs to be looked in to. Either someone is working weird hours or you might have an intruder.
Cause they don’t need the data when they are sleeping?
On a more serious note, it would probably be a side-effect of some bigger problem.
e.g. “we’re running SQL Server backups onto a drive with only 300MB free and the business won’t buy us another one, despite the loud pleading we give them every week about it…”
I’ve seen it done, never really understood why. I suppose it does reduce the number of restores/files needed to restore to a STOPAT point-in-time …
… but there again we increase Log backup frequency to every 2 minutes during overnight housekeeping – index rebuilds etc. – because those tasks fill up the Log far faster than our Real Users do! If we reduced Log backups from day-rate of 10 minute interval to night-rate of 30-minutes our Log File would be extending, for sure.
Developers who keep crashing servers and writing horrible queries and burying them deep in nested SSIS packages and have emotional problems and cry at least once a year when their Rube Goldberg package runs over 8 hrs with no output, but then insist that learning to code correctly is “above their pay grade” – these people get rudeness.
I am not a therapist.
Don’t act like a therapist, either! That’s actually worse. You should still be respectful– because being disrespectful just isn’t in your best interest.
Being respectful doesn’t mean that you can’t give direct, effective, honest feedback. It just means you do it at the right time, in the right place, and in the right way.
Where’s the +1 button?
HA! I thought I was the only one who thought like that! 🙂
Kendra is correct though… it’s only 95% of the developers that give the rest a bad name. There are some out there who are very good at what they do and don’t actively strive to find new and better ways of writing worse code than previously thought possible.
Those have been the ones who have saved my bacon more than once.
It’s the majority who scream and cry that something is urgent when they call at 6pm on a Friday night saying that something is not working – but when you inquire if they are going to be around and the reply is: “Oh no, I’m going home now….”, it’s really not that urgent.
I’m (now) a firm believer in #1, but it took a LONG time to see it. To be fair, it goes both ways. Devs can be just as obnoxious as DBA’s.
The truth is we all can, this is not limited to us technical types. A painful lesson to learn is that even being “right” is no excuse for acting like an a-hole. If I could go back in time and teach myself a single thing earlier, this would probably be it.
I used to have a boss that would ask me: do you want to be right, or be happy? I always replied “both”, but sometimes that’s tricky to pull off in practice. 🙂
Ah, Douglas Adams! The conversation between Arthur Dent and Slartibartfarst.
Slarti: “…So I told them I’d rather be happy than right any day.”
Arthur: “And are you?”
Slarti: “No, that’s where it all falls down.”
Kendra, I think the one decision I regretted most about being a DBA was saying, “Yes. I’m willing to support your SQL 2000 production database that has to be restarted everytime the SQL Agent locks up and replication fails.”
But also, more common to more people is maybe “Ok, I fixed this, this, this and this. I need to remember to change *that* when this is all deployed.” — Without a deployment plan, or other SDLC methodology… that *that* is gonna get forgot.
I think a good addition would be not sharing information. I’ve seen DBA’s and Developers who are guilty of keeping important information to themselves for fear of losing their little empires.
Nothing is worse than when another DBA goes on vacation and you get a call from one of his/her “support buddies” that gives a really vague request and when you ask them for more information they reply with, “Bob used to do this for me every week and I just ask for him to run the fix scripts.” Later I find out that those scripts are in his/her home folder.
I try to share everything I can, even to the point of training my own replacement as I don’t want to be doing the same thing for years and years when the company can hire a younger guy I can train so I can move on to bigger and better things, like working with the development staff on database design so the database will run faster.
Oh, great point! Hoarding knowledge is the worst.
This also reminds me of kind of an inverse problem that I’ve seen some new DBAs hit: they may be too trusting of people who ask for access, backups, or copies of the data. You’re in a very tricky position when you manage these servers, and you have to be super careful about who you let see the data, what you take home with you, and the information you share with others. It can be tricky socially to make people wait while you make sure it’s OK to let them have something, but it’s always worth it to check.
My thoughts
#1 Little knowledge is very dangerous
The main issue I have with Dev’s is lack of knowledge despite the fact they ‘know’ SQL.
Having spanned the divide with multiple roles down the years I have seen both worlds, though my feet have been mainly data side. I have interviewed many ‘Senior’ Developers who say they know SQL but don’t really know what to do if you put an Execution Plan in front of them. A little bit of knowledge is quite dangerous in this respect, and probably a downfall in most applications. Currently my work adheres to Parito’s Principle (the 80:20 ratio). Most of the issues I have to deal with are where a developer has built an application that relies heavily on a DB, without really understanding the DB side. Or even worse where the application has been built code first using an ORM. nHIbenate is particularly poor, though Entity is slowly improving.
I have worked with many a developer, most of which have been excellent. However the are a majority I would say that either don’t like to ask for help, or see asking a Specialized Data Dev/DBA type folk as a weakness or seriously un-cool.
However Devs who have worked is Software are pretty good compared to
#2 Legacy applications built by wannabe Developers
We have a few of these that we have to support, applications built on databases by colleagues who have again a little bit of knowledge, though mainly with spreadsheets and converting that over to a database. Lines upon lines of dynamic SQL built with loops and cursors. Updating wide tables one cell at a time. ie looping through columns, then rows with a separate update for each ‘cell’. (anyone here that log file screaming). Problem is, the attitude of these ends up being a, we don’t need your support, we can do this ourselves however it doesn’t seem to work, can you just fix this error for us. At which point you have to run down the rabbit hole of a dozen (if you are lucky) nested stored procedures all of which call other sp’s based on results of loops upon loops upon loop, all dynamical. It feels like you are expected to be some sort of alchemist however of course your not allowed to change anything, so you end up having to polish it at best
and finally
#3 No body really knowing what my role is, or even what my skillset is
Sharing an office with 5 .Net Devs and being the only DBA/Data Developer I often sit over hearing conversations about code interaction with the database, or someone trying to redesign the database and thinking ‘should I get involved’?, how much pain is going to be, would it be better to intervene now or later (when the code is all ‘compile’ and we have to break all of best practice to ge the application to work with the database for more than half an hour).
I also get, can you set up this hardware/network/vm etc etc. We have a production cloud db environment running that I am only meant to be maintaining when I not on some project to sort out the above. (ie I shouldn’t be booking time against it).
As a DBA I love learning, and if anyone can show me something new and how to do something better/fast/more secure, etc then I’m all for it. And if I’m proved wrong, I’ll accept that.
What grinds my gears is the Devs who aren’t open to this learning, the ones who are a total closed shop refuse to admit they are wrong, or accept blame (you know, the ones who still insist blanket NOLOCK everywhere is fine.) And they constantly demand sysadmin access for PROD.