It was early one morning and I was working on a production deployment to add a new Subscriber to an existing Publication. A routine task and one that I had performed many times before.

With SSMS open in front of me, I highlighted the relevant portion of my deployment script (sp_addsubscription …), double checked my query window had the correct connection and hit F5 like a boss.

Whilst I waited for the SQL magic to happen, I reached over and grabbed my still fresh coffee, proudly basking in my hard earned seconds of freedom. Glancing back at SSMS my query was still running. More coffee then….

Checking again, the clock read 15 seconds. This is strange I think, staring intently at the output window anticipating those beautiful words “Query executed successfully.” at any moment.

20 seconds, still nothing.

25 seconds. Okay, something is not quite right here but what? This has never happened before. Dumbfounded that anything could possibly be amiss I decide to simply give it some more time whilst I think on it.

Suddenly I’m interpreted by a bombardment of instant message pop-ups and the sound of my desk phone screeching at me like a siren. I grab it and ignore the IMs.

“Good morning John speaking”, I say calmly, although bricking it on the inside in anticipation of what’s coming.

“Hi John, we’re seeing a drop in the booking trend….”, states the caller from the operations team.

Instantly my world freezes. I become strangely aware of my own heart beating, accompanied by a sinking feeling in my gut.

“Okay, looking into it now”, I reply.

I open a new query window, type “sp_WhoIsActive“ and hit F5. I stare as the results pane grows before my eyes revealing several hundred blocked spids. They are all customer booking related(think people trying to buy stuff), queued behind my replication query. WTF!

Customer experience is our number one priority and so I immediately make the decision to terminate the lead blocker by hitting the red stop button(Cancel Executing Query) at the top of the first query window. It stops immediately, good.

I execute sp_WhoIsActive once again and am greeted with even more blocked spids than previously. Why is the replication spid still open, and blocking? Blast!

I flip back to the replication query window, type ROLLBACK and execute it.

“Command(s) completed successfully.”

Okay good and I confirm the same via sp_WhoIsActive, this time no blocked spids reported. Excellent, all previously outstanding activity had completed successfully and normal business operation had resumed.

When A Good Result is Not Good Enough

Some of you may have read my story and be thinking well done old boy, good job there getting things sorted but you’d be wrong!

A lot can happen in a couple of minutes and I was unprepared for what did play out on this occasion. Sure it was an unpredictable event that I responded to quickly, avoiding any loss of revenue. The business folks may have chalked it up as a win but I sure have not. If you ask me, I got lucky.

I was overconfident and this lead to my being complacent. It never even occurred to me that something could potentially go wrong. I’d performed the task so many times before that I simply assumed this time would be no different.

My overconfidence on this occasion had caused me to ignore one of the most vital DBA survival skills, Thinking Defensively.

How You Can Adopt a Defensive Mindset

Both as a reminder to myself and so that you too may avoid this particular trap of overconfidence(complacency), I want to share here with you my key points for adopting a defensive mindset.

Our database environments are business assets and as DBAs it is our responsibility to guard and protect them.

Before you implement any administrative tasks whether it be deploying a set of database patches to your environment, modifying table data or creating new objects, whatever changes you plan to make, I advise you to always adopt a doomsayer’s frame of mind before carrying out the implementation.

We all spend a lot of time bolstering our knowledge and developing our skills. It’s natural to reach a comfortable place in our assessment of our own abilities but don’t let this develop into overconfidence, which can lead to complacency if not careful.

Adopting a defensive mindset such as answering these questions, together with keeping a look out for signs of overconfidence both in yourself and your team, will help you on your own path to being an outstanding data professional.

Thanks for Reading. Make sure you don’t miss my future posts! Subscribe and get posts delivered direct to your Email inbox for Free.

Check out the Professional Development category on my blog for similar posts.