T-SQL Tuesday #92 : Lessons Learned the Hard Way - SentryOne Team Blog

T-SQL Tuesday #92 : Lessons Learned the Hard Way

T-SQL Tuesday

This month's T-SQL Tuesday is hosted by Raul Gonzalez (@SQLDoubleG), and is about Lessons Learned the Hard Way.

Now, I've learned many lessons the hard way. When I started my career and transitioned from web design and server-side scripting into more focus on SQL Server, I bought a whole bunch of books; my favorites were those by the late Ken Henderson (I still have them). Sadly, I skimmed all of them – I preferred to learn by doing, and sometimes I had to learn really quickly. Being in a startup meant turnaround times were often rushed and haphazard. Rather than learn up front what might happen with, say, ntext and image columns at scale, I would learn much later.

I learned so many things the hard way – often after getting used to doing them in a suboptimal way – that I've assembled a lot of those things I've learned into a popular conference session ("Bad Habits to Kick"), the better part of a full-day workshop, and a long-running blog theme.

One of the hard lessons I've learned multiple times is running queries without double-checking accuracy. I'm talking about deletes or updates against the wrong environment, with an incorrect WHERE clause, or with no WHERE clause at all. Guess what happens if you accidentally delete all the rows in a table in production? If you're not log shipping on an intentional delay, you're restoring from a backup. Which is time consuming, disruptive, and error-prone itself.

So, in a VM I dedicate to production-related administration, I modified my New Query template, as I described recently in this MSSQLTips post. Basically, you open this file:

C:\Program Files (x86)\Microsoft SQL Server\[140|130|120|110]\Tools\Binn\ ManagementStudio\SqlWorkbenchProjectItems\Sql\SQLFile.sql

I changed this file so that – again, only on this particular VM – every query window I open using the New Query button on the toolbar starts out with the following code:

BEGIN TRANSACTION;
 
 
-- COMMIT   TRANSACTION;
-- ROLLBACK TRANSACTION;

Now, if I open a new window and type some hair-brained query really quickly and hit F5, I might hopefully notice when the number of rows affected is different from what I expected. In that case, I can hightlight the ROLLBACK portion and hit F5 again. Much easier than restoring from backup, right?

Of course, there are other potential issues with this. Most importantly, this makes it possible to type up a perfectly valid query, hit F5, then go to lunch. Which won't cause data loss, but it could cause enough blocking to have you dusting off your resume when you get back from lunch.

If you're prone to running queries quicker than you can proofread them, don't learn this lesson the hard way – do something to protect your production data from errant DML.

Comments ( 8 )

  • Nigel Ainscoe says:

    I do this too, but I leave the rollback uncommented so I have to deliberately choose to run the commit and I'm less likely to leave an open transaction lying around.

  • ol says:

    done that. Updated busy table. Blocked 1000s queries in seconds. The server become unresponsive

  • Longtime says:

    When working like "..hair-brained query really quickly and hit F5.." in a production environment, then you need a safety net like this for sure.

  • Kenneth Fisher says:

    My favorite is adding raiserror to the top of the new query template.

    RAISERROR('You"re not supposed to run the entire script!', 20, -1) WITH LOG
    GO

    This way if you accidentally run the entire script instead of just the portion you are working on right now it fails :)

  • Nate Arnold says:

    This is a fantastic tip – I didn't know you could modify the new query template. Thanks!!!

  • Bert Wagner says:

    Excellent idea, I never thought of doing this. I'll be using this template for all major production changes going forward. Thanks for the idea.

  • Alex Friedman says:

    Check out the latest version of SSMSBoost — they included a TRANCOUNT check, and pop up an alert if you leave an open transaction. Super useful.

  • Denis Gobo says:

    Aaron,

    I did this same exact thing with open tran as well

Leave A Comment

Your email address will not be published.