Recently I wrote about the disaster recovery issue recently at SQLServerCentral. Someone sent me a note asking why I didn’t set a transaction when doing the INSERT in case I made a mistake.
Two reasons. First, I could undo the inserts since they were simple. I actually had the IDs and it would have been easy to delete the data.
However this was a live production system, and the area in which I think these posts are in the database was receiving a ton of activity. I was watching my email, and could see responses coming in from other threads that are physically near this one in the clustered index.
So I didn’t start a transaction since I was worried about locking and timeouts on the site. I couldn’t easily check things since I can’t see the web on VPN and needed to disconnect, drop my RDP session, check things, and then go back. Despite the idea that my remote session shouldn’t get killed (and thus the transaction), I didn’t want to take the chance.
The decision to begin a transaction on a production system as a DBA, especially one that might take their time checking the impact, should not be taken lightly. If you do start one, make sure that you have your test scripts ready to go so you do not hold locks any longer than necessary.