SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Confessions of a DBA – Part 2

How I caused a fifteen minutes of blocking.

This particular incident happened when I joined this company. I joined this company as a network administrator/programmer/third party software expert. My boss used to call me Jack of all trades. This was my third project and I was feeling quite comfortable with the role I was given.

My boss handed me a project to clean out a table in production that had thousands of wrongly entered data. This table was being queried and updated all the time in the production. At that time, every tom, dick and harry was a sysadmin in the production DB box. Like any good developer, I tested out my script in my test environment. Everything was fine and dandy. I showed the results to my boss and he was happy with it. He asked me to go ahead and do it in production the next day.

On the D-Day, like with any good release strategy, I started a transaction so that I did not want to get any nasty surprises when I ran the script. I could always roll back the transaction. The script took nearly a minute to execute. I was running some select statements to make sure that the clean up worked fine.

All of a sudden there were phones ringing left and right and every developer and support was scrambling around. The users were complaining that the applications were hanging or crashing. I too jumped into the fray trying to figure out what was happening. After ten minutes of digging around I found out the problem. There was a high amount of blocking going on the database. I told my colleagues regarding this. Then it suddenly hit me. It was a DUH moment. After the clean up was completed, I did not commit the transaction. I quickly committed the transaction and everything started going back to normal. It was embarrassing to tell the boss that I caused the blocking that I was investigating.


Posted by Jason Brimhall on 13 April 2011

I have seen several people do the very same thing

Posted by sean redmond on 15 April 2011

I would regard it almost as a rite pf passage: A mistake that one makes only once.

One learns in a very real way what effect a DBA has on the running of the system and the company. Leave a transaction open on a production system and watch the company come to a sudden halt.

However, in the defence of the poor DBAs who are caught out with this, (although in this case they are to blame and there is no two ways about it) it would help to let the DBA in question finish the work being done and not distract him/her with other tasks.

Posted by matthew.lowe 19651 on 15 April 2011

I like the fact that in SQL 2011 SMS puts in Begin and Commit transaction for you , saves you the hassle of not commiting it .

Posted by Roy Ernest on 15 April 2011

Sean, True. It was my mistake. I should have known. But I never made that mistake again. :-)

Leave a Comment

Please register or log in to leave a comment.