Stupid Stuff I have done. 2/?. Sometimes even a dev server is not a good dev environment

Sometimes you can't win for loosing.

Two weeks since I took the job as the first DBA ever for a company, we upgraded from SQL 2000 to SQL 2005.  They had had a consultant in there to do the prep work, then let me loose to do the upgrade.  My personal, in production experience with SQL 2005: none.  Good times!

This, of course, sounds like the beginning of a horror story for DBAs.  One we have heard over and over.  This time it was however a fairly pain free upgrade.  Couple of DTS packages that had not been properly converted to SSIS, a few performance issues, but nothing major that stopped business from going on.  Success!

After a couple of weeks in production, I started getting curious.  Some of the problems I saw stemmed from Access based queries taking up way too much time.  Being new, I had no idea about who or what used Access, or how much it was used.  A few casual questions to the developers and some IT managers did not yield too many good answers.

This is when I think to myself "how could I track how often Access is used?"  I could do a trace, but that seems so old school.  "hmmm aren't there some new DDL triggers for 2005?  ohh what is this? Login triggers?"  Yupp.  Time to play with login triggers.

Maybe I can make these actually see what application the connection is coming from.  It's in sp_who2, which would mean it has to be part of the login process somehow....  After some digging, sure enough, I find some information about how to do this, go about my way coding a login trigger, with inserts into a table of the login information I am interested in.   Simple enough right?

As soon as I executed my code, the first line after it in the output says:
"You have been disconnected"

uhhoooo

This can't be good.  Try to connect again, nope, try with the sa account,  nope.  Did we enable DAC (Dedicated Admin Connection) on this server, surely that would get me in?  NOPE.

Oh this is NOT good.  About this time I stand up to walk over to my boss.  Developer after developer head is popping up over their cubes.

At this point I am actually laughing as I make my way over to my boss.  Tell him I managed to get everyone, including myself and the sa, locked out of the DEV server.  Also let him know I am going to try to restart it in single user, admin only mode.

Fingers crossed as I shut down the service (thankfully THAT worked).  Find the proper command line command and start up the server again.

ok, command window says it is up.  Start up management studio and try to connect.  SUCCESS!  Disable the login trigger (ok, I admit it, I deleted it completely just to be sure).  Restart the server in normal mode.  Phew.  Server works, developers can get back in,  everyone are happy.  Anders is a (somewhat) hero.

So what happened?  XML.  Or rather, a tiny bit problem with the xquery to get the data I needed out of the login data to put it in the table.  This caused the insert to fail, which rolled back the login.  Yikes.

  So what did I learn from all this?  Sometimes even a development server is too important for a DBA to develop and test on.  Sometimes it calls for your very own SQL install, what I like to call a throw away SQL install.  Since then I have always had a local to my own PC SQL install to test these kind of things on.