A quick 'save me from myself' thing in SSMS

Have you ever felt that 'down-elevator' feeling in the pit of your stomach when you realize you just executed a script against the wrong instance? I have.

It's of course possible to add name checks and all to only allow execution on a given instance, I'm going to go out on a limb here and guess that most people don't do that in most cases.

Happily, there's a quick and easy setting you can take advantage of to give yourself a visual indication that hey, maybe you're not where you thought you were: the 'Use custom color:' setting in the Connection Properties of the Connect to Server dialog box.

To get there, first click Options:

Check the box for 'Use custom color:', and hit 'Select...':

Pick your color, click 'OK' (I tend to use red for production, orange for QA/test, green for development, but go with whatever works best for you), and then click 'Connect':

From here on out, the status bar down at the bottom of the query window will be whatever color you've picked for that instance:

While various third-party tools offer similar functionality (often with a lot more flexibility and configurability), this is built-in to SSMS and has been around for quite a while. It's a great quick gut-check!

Updates and housekeeping

After... well, forever... on an old theme, I've updated. Some older posts may look weird, but most should be good. Please let me know if you run into any problems.

The code I can't live without

Today's T-SQL Tuesday topic is thought-provoking: What code couldn't you live without? (OK, OK, technically it's just titled 'Code You Would Hate to Live Without', but work with me here.)

One of the hallmarks of the dedicated DBA is that we write our own tools. I've encouraged juniors to write their own code, even if they can find things online that solve their problems (inexperienced folks blindly using code from online is a whole can of worms that I may open later). We gain so much by doing the work to learn about the systems we're interacting with, and the harder work of figuring out how to write the code, be it T-SQL, Powershell, or what have you, to really dig in there.

As a brief aside, in my experience if you don't have the curiosity and drive to begin writing your own tools, you're not going to get very far as a DBA. I'm not sure I'd WANT you to be in charge of my servers. Now, not for a second would I expect highly-polished code from juniors right out of the gate. What matters more is evidence of that curiosity and drive. What matters is that we keep learning, keep practicing, and keep improving.

On the flip side, there are times we find something out there that Just Works. Part of the wisdom we develop as we progress is knowing when to press on with writing our own, and when someone else's code does the job perfectly.

I'm finished digressing - on to the code I'd hate to live without!

Under the 'code from others' umbrella, code I'd hate to live without includes:

• Ola Hallengren's maintenance solution. Oh my god you guys, if you need a robust backup and maintenance tool it's well worth your time to consider Ola's. I've used Ola's solution with great success in both small and large environments.
• Glenn Berry's diagnostic information queries. These are priceless. They cover almost anything you need to gain insight into your server's health and status.
• Adam Machanic's sp_whoisactive. What's going on, in detail, RIGHT NOW!
• sp_who2 filtered, from SQL Matters. Simply putting sp_who2's output into a table is brilliant, and one of those things that I wished I'd thought of myself when I came across this. 

Of the things I've written myself, I would hate to live without these things (please note, these are all hosted in Dropbox; if you want copies and can't get to Dropbox please let me know!):

• Replication scripts!
• A quick script to get publication information. 
• This returns publishing database, publication name, the source article name, subscriber instance, and the immediate sync setting.
• A trio of scripts I wrote to get replication agent history - distribution, snapshot, and log reader. 
• These scripts return agent names, agent IDs, status info, and the most recent 100 history records. Adjust the value as necessary.
• A script to identify the jobs associated with various agents.
• A script to get stored procedure execution information, and the query plan if you want.
• This query gives a lot of info - when the plan was cached, how many times the plan's been looked up, how many things reference the plan, when the proc was last executed, how many times it's been executed, all sorts of worker, reads, and writes info including averages.
• The usual caveats on the cache info apply - restarts, cache flushes, index rebuilds, plan recreations, etc. are all things to keep in mind
• Some templates for file manipulation
• Moving database files
• Restoring a database

I've got a lot more, but these are the most important to me.