When-Others-Then and other Troubleshooting Monsters

I’m definitely having one of these weeks.

We have this insanely complicated, highly visible, ultra high priority project going live this this Wednsday. The DBA who worked on this project nearly full time for the last 6 month left on 3 weeks vacation on Monday. I’m the replacement.

Tuesday morning, I’m on a conference call. The night job failed due to an issue with “dblink”. Must be fixes ASAP or the go-live will be delayed. I was all “Wait. I’m just a simple DBA. What dblink are you talking about?”.

So the morning was spent with me trying to slowly work my way through huge amounts of night-job code. While everyone around me was running in circles screaming. Mostly screaming “network” and “dblink”.

You know what I found out? That we have no clue why the night job failed. Because at the end of every single procedure in the job was code that said:

EXCEPTION
 WHEN OTHERS
 THEN
 INSERT INTO NIGHT_JOB
 (status, last_run_stmp)
 VALUES ('failed', SYSDATE);
 COMMIT;

The job failed. What more information could you possibly want?

And the funny thing that happens when you have such useful error messages, is that everyone starts developing theories about why the failure occurred. Maybe someone tested the dblink few hours after the failure occurred, noticed that the remote site is down, and decides that this is the issue. Someone else tries rerunning some of the code and gets “unique constraint” error, so he guesses that this is the issue. But of course, its all guesses after the fact. No one can know what caused the night job to fail on its original run.

Trying to troubleshoot an issue based on guesses about what was the error is a complete waste of time – what are the chances that you’ll find the real issue and that the next time the night-job runs everything will work?

Proper error handling is a complex topic – which exceptions can be handled locally and which should be raised? At which levels do we trap exceptions? These questions are a topic of much discussions among developers and architects. But the basics of error handling should not be ignored – When an error occurs,  we must know what was the error.

Invisibility

Ever since I was a young kid, I was fascinated with superheros and I’ve always wanted to have a superpower. Now it looks like I got what I asked for and somewhere in the last year I’ve become invisible.

We are having a lot of “End of Year” talks in the company, at all levels. Teams, departments, organizations, business, etc. You know how these go – what did we accomplish last year, what are our plans for next year. So, in the department meeting we talked about the improvements in the applications and new customer demands for the next year. In the organization level we talked more about sales, strategy and plans to cut costs.

Listening to all those discussions, you’d come to the conclusion that we have customers that are paying us a lot and have high demands, they are using applications which we keep upgrading. These applications run on servers, which cost a lot, so we are moving to VMWare, and usage storage which is also expensive but there is nothing to do about it. Everything is hosted in a data center which costs way too much, so we will be moving to a cheaper one. We also have nice processes that we keep improving and internal tools that we are building.

It sounds like we don’t have a database anywhere. Which makes me wonder what is it I do all day. Or more accurately, why the DBA team fails to communicate our value to the business and whether it means that we will be the first team to go when downsizing starts.

When I am not allowed to do a good job

So, we have this customer who has way too many grants and permissions on our database. This kind of thing should have been illegal, but sales sold him a solution that required this and we were forced to cooperate.

Now this customer requires from us about twice as many resources as all the other customers combined. Maybe more. Theoretically he got all those grants so he can administer his own instance and we shouldn’t be involved with him at all, but practically the customer and his DB developers bring the database to its knees every few days, then they blame us and we have to go in, prove it is not us and in the process fix the issue for them.

Now, we’ve been involved in enough of these fires to know much of their code and processes inside and out, we know where they coded to unrealistic requirements (poll ever second a table that updates every five minutes), and where the requirements were ok but the code could be optimized. In fact, we believe we can clean up their code enough to prevent many of these fires.

The customers will be happy if we help them fix the code, they don’t enjoy the frequent problems any more than we do. We’ll be happy to fix the code two – every decent DBA will prefer to fix code rather than spend few hours a week killing processes and writing emails documenting what he did and what the developers should be looking into.

So why don’t we do that? Because the customer didn’t pay for us to do “DBA work” for him. He paid for the instance and for the liberal access, but not for the DBAs. So, DBAs are not allowed to fix any problem with the customer code or do any other DBA work for him. We are only allowed to shoot processes that cause CPU or IO problems on the server itself, send the customer trace files, and reboot the servers when the customer requests it. Never mind that this decision is actually costing us *more* DBA time, and therefore the company can both save money and make the big customer happy by giving him more than he asked for. Never mind that DBAs are usually much happier when they are actually allowed to do their job. The most important thing is that we are following our contracts to the letter.

*

Jeff the SQL Server blogger explains that distinct is not a function. You have no idea how many times I’ve seen this mistake (and even did it myself once or twice).