SQL Dumbass

We have an upgrade to an application being deployed and as part of the upgrade there is a database piece. The app team decides (rightfully) to make certain that no one tries to connect to the database while the upgrade is happening, and they tell their users to not use any apps that connect to the database. So far, so good.

Well, one account continues to open a connection, and no one has any idea about the account. I recognize it and point towards the person on a different team, as it is a service account used by a web pool to connect to the database. Unfortunately, it cannot be turned off anytime soon, as we do not host the servers making the connection. So, what to do?

I decide to put the db in single user mode, kill all connections, and have them run the upgrade. It fails, because the upgrade opens about thirty connections to the database. Why? Good question.

I decide to switch the database to dbo use only, take it out of single user mode, and have them run the upgrade. Before the upgrade starts, that service account connects again. Turns out the account is a sys admin. Why? Good question.

We remove the service account from sys admin and try to get things going again. It reconnects. Since it is not a sys admin, and not a dbo, and not an alias, we are stumped as to how it can connect. You would think that I would know the answer as to how it is getting in, but I don’t. Suddenly, my colleague realizes that we could lock the accounts.

Lock the account! Why did i not think of that! We lock a few accounts, just to be safe, and the upgrade gets underway, only a few hours later than desired.

We have a sql 2005 database server in mexico.  It has a couple dbs on it.  Backups should have been setup on it, right?  Well, no.  No backups.  No maint plan, no backup to tape.

So I go to the db, and create a maint plan.  Simple… let’s backup to the local drive, with sub folders.  Let’s then go and make the cleanup steps for all the db’s.  Simple.  Schedule it, and viola! we have backups!

Couple days or weeks go by, and I check back with the system.  No backups.  Hum… odd…

I look at the job history, and the maint job has failed each and every time.  It simply says it could not start step 1. 
I look into rights?  Is the sql agent configured correctly, running under a good account? Is the owner of the job correct?  Are the steps in the job setup right?  Linked to each other properly?  Anything…

After a while, i give up on that maint plan, and create a new one.  A simple one.  Backup 1 db only.  It fails too.  Same error. 

I finally turn to google with the error message, and come across someone that had a similar problem.  The job step has a property for the package.  It was missing a slash at the beginning of the value.  If i edited the step, and added the slash, all was well…  backups going, job succeeding, universe was righted.

Who’s the dumbass now?  yeah, it was me.

Frantic developer storms into the office wants to know the meaning of the error that has just appeared on their screen is….the error is something similar to : insufficient memory space in database…

Now, without a sqlcode or msgno or anything else I go off to the magic world of google and BOL looking for an explanation nothing, nada, zilch….  which was really to be expected, I mean thats about as generic as an error can get.  After a while the developer comes back and will not accept my answer of this cant be a real database issue and to come see me when they get a real dbms error code.  So, after a bit of prodding I turn on a sql trace for the developer, guess what shows up in the trace.  not 1 single SQL, at this point the developer is looking beaten and retreats to their office.  A few days later I ask what ever happened to that error and you’d never guess that I was pleasantly surprised at the great story that was told. 

After spending at least a combined 20 hours the developer decides to call the vendor of the application for some support.  the vendor has a great explanation, Apparently this is a common problem, the client disk space cant be a number divisible by four or this error occurs, all we have to do is create a few 100mb text files in the temp directory so that the free space isn’t divisible by 4 and the problem will be solved.  at this point all I can ask is “Are You Kidding Me?”  and sure enough, modifying the disk free space fixes the issue, nope no database problem here.

All in a weeks work I suppose!

Gentleman says he is from Microsoft and has a couple questions about Microsoft databases.  He asks if I use the database…. I say yes, and in fact, i am editing a dts as we speak. 

He asks me what versions I have used.  We have both 2000 and 2005.  But nothing yet using 2008, or developers even using a CTP. 

He proceeds to ask me what i like most about the new version, SQL 2007.  No i didn’t typo the 2007.  He asked me that…. I correct him and say 2008.  He makes a crack about how funny the name is, that its 2008, when its being released in 2007… i sigh…

So, now, I am painfully aware of this individuals knowledge of the product…

He continues to ask me weird questions, and i provide apparently confusing answers.

At one point he asks me what my title is.  I respond, DBA.  He tries to guess what the letters stand for… and give a couple good tries, but fails.  As i explain the acronym, my wife cracks up in the background.  Even she knows what it stands for, and was mystified that this person had no idea. 

From there, the call went downhill.  Fun.

I was sitting in a meeting room along with two application analysts.  They called the meeting for the purpose of reviewing a new application we were migrating towards.  Both the current product and the new product were dependent on SQL databases so I was involved from a standpoint of the DBA.  Both analysts were from the same team and were in the process of a warm hand-off since the two products would be under the responsibility of different analysts from an application standpoint.

We reviewed schedules, database specifications, security items, networking issues, and hardware requirements.  The nature of the database warranted hosting the database on a dedicated database server and the application footprint would reside on its own application server.  We discussed these hardware requirements for at least 30 minutes of the 45 minutes spent in the meeting.  Both analysts have been with the company for years, handling quite a few applications each.  Sure, the one was a little flighty, but she was on her best behavior during the meeting so far.  My opinion of meetings is well noted in my company.  To me they serve no purpose other than to waste time and hone PowerPoint skills.  However this meeting was progressing nicely.

“This is great, there are no questions; we’ll be done with this meeting 15 minutes early!” I thought. 

As I was gathering my notes and coffee mug I stood up and then heard THE QUESTION.  Nothing good has come from a question that starts with the four words I heard uttered by “Ms. Flighty” but they escaped her lips nonetheless.

“I was just wondering,” she voiced up as my hand was on the door knob of the conference room, “What is the difference between a Database Server and an Application Server?”

The meeting ran for another 45 minutes while I went an led a 100-level IT class for Ms. Flighty and her teammate who shot daggers from her eyes for the better part of the extended meeting.  Did I mention that Ms. Flighty was an application analyst for well over 10 years at various companies?

Clients db is runing slow.  I hear about it thru the normal grapevine.  I look into Spotlight to see what is going on.  Sure enough, seems like its getting hit hard.  But before i can investigate more, it clears itself up. 
Few minutes later, slow again.  Clears up again.  Something up…
So start digging.  Notice a job is always running when slowness is reported.  Start asking questions.  Turns out some new code was rolled just the other day, that is called from this job. 
Lets look…
First proc i look into has an odd query.  Seems we are joining an invoice_no field between tables, but we dont know if this data has been scrubbed, so its being sent to a function to scrub it.
  WHERE  DBO.UDF_REMOVE_BADCHARS(H.INVOICE_NO) = DBO.UDF_REMOVE_BADCHARS(P.INVOICE_NO) 
Wow, nice code there… lets do a table scan… and this is done in multiple places, in multiple procs, which are all called from this job, that runs every minute… wooohooo

rollback.  rewrite.

(I know what you are saying now… how did this get by me?  well, we are not part of the code review process.  We just roll it.  ARGH!!)

I’m minding my business.  Working on this and that.  And behind me, near me, i hear someone asking for the install key for sql 2005.    I think to ask whats going on, but don’t and get engaged again in what i was doing.
Later that day, i hear someone else asking for the same thing.  Again, i get distracted with real work.
Later even, i overhear someone else asking for it via email and IM.  Then i finaly get an IM from someone, and in passing, they ask for a key…  I ask them why they need it?  Has your install prompted you for the key?  Im my memory, ive never put in a key… I know i have a key on the cd i have at home…  but my memory is fuzy.  ITs been a while since i installed it myself… So i ask the silly question.  Has the installation prompted you for a key?
The dumbfounded response was, “No, I have not yet tried the installation.  I just assumed…”
how many man hours spent on this today?

Developer creates a roll document.  Listing out all objects that need to be rolled.
When its time to roll to production, we review the document with multiple groups, assuring ourselves and our process that we have covered all the bases.  DBA takes roll document, and performs a sync between UAT environment and Production, syncing only the objects that are listed in the roll document.
Fast forward 1 day….
Production support realizes, thru a belated testing, that some functionality is not present.  Tempers flare.  Developers are blamed.  DBA’s brought in too help troubleshoot the problem….
Fast forward some time….
The roll doc that the DBA used was not the correct version.  While on the group call, the developer made last minute changes to the roll doc.  These changes never made it to the one the DBA used.  Some objects were missed. 
Yeah.  Another dollar.  Another broken process that is blamed on someone other than the creators of the problem.  Makes me want to truncate all data, and delete all users.  Fix that one…

Database server has little space on 2 drives.  Database is rather large.  Backups can bee way too big.  We use Litespeed to shrink the database backups down.  Even so, backup files are large, compared to the size available.
Something happens and the log file grows way too big.  Backups are kept for 6 days, and hard drive fills up.  Of course this happens Sunday around 10am…
I spend some time removing files, freeing up space.  Enough to get the server limping to Monday.
Monday, i start freeing up space again.  Truncating the log file.  Getting the db ready for the day. 

Production support individual puts in a ticket.  Then stops by my desk.  Tells me, guess what…the log file is full, could i look into it. 

What do i do?  I snap at him.  Inform him ive been working on it all morning… and was working on it Sunday….
Then i go back to cleaning up the mess….

We have a system that has some simple replication going. Used mainly for reporting.  Some data is gathered from 4 dbs, and stored in a reporting database.  Some of the things that are stored are quantities of different doc types.  These are displayed on the main menu.  For some reason, some of the menus are from the real db, while others are from the report db.  This can lead to a disconnect in the values, when replication has been turned off…

Fast forward.  System needs an upgrade of the product and db.  So replication is disabled.  Upgrade proceeds.  This takes a day plus of multiple man hours.  Luckily, the DBA is not needed.  So he goes on to other tasks. 
A day or so passes, and production support individual creates a ticket that the numbers are off… please investigate.  This finally reaches the DBA, and the DBA is reminded that replication was disabled, and never reenabled after the upgrade.  For some reason, this step is always skipped by the upgraders…
DBA reminds that the upgraders need to tell him when they are done, and replication can be enabled.
DBA reenables the replication.  Asks prod support to review to verify accurateness.  While DBA is requesting review… guess who puts in another ticket… yup… prod support guy lets us know again… numbers are off…

Oh, and this is about the 10th time this has happened.  Exact sequence.  Each time, DBA requests further knowledge of completion, so replication can be reenabled.  Never occurs.  Repeat.