It’s TSQL Tuesday again! This time our respected host is Bert Wagner (b/t). Quick aside, if you haven’t seen his vlogs before take some time and watch a few. They are entertaining, informative and best of all short :). I’m really enjoying the work Bert! Keep on trucking!

Anyway, this time around Bert wants us to post some code that we use that we just can’t live without. That’s a bit hard for me since I’m constantly posting about code I’ve written or enjoy using. So here is my solution (<- pun there, you'll get it in a second), a while back I wrote about using solutions (see? I thought that was rather funny myself.) in SSMS to store the scripts I use on a regular basis. So I thought this time around I would go through and create a list of links to the sources for many of the most useful scripts.

Categories:

• Notes are just that. Pieces of scripts, or a series of scripts to remind me of things.
• Query means that this is something I can open and run without much input. Frequently there are commented out parameters or variations. But in general I can just open and run.
• SPs These are create scripts for SPs that I use on a regular basis. I keep them here because I work with hundreds of servers and I’m never sure which ones have them and which don’t. Also a lot of times the documentation is at the top of the script.

 
Creating the solution

• Notes – Debug Database Mail
• Notes – Extended Events: No link here since these are just random sample queries of some of the system tables.
• Notes – xQuery: Pretty much the same as above. Just some random samples I can use to copy and paste.
• Query – Generate DBMail setup scripts: I haven’t tested this a whole lot yet, but it seems pretty good.
• Query – Drive Space
• Query – Index Stats last Updated
• Query – Lead Blocker
• Query – Missing index by query
• Query – Performance: Another one with no link. This is just a simple template of a query on sys.dm_exec_query_stats and sys.dm_exec_procedure_stats
• Query – Search the error log
• Query – Shred The Deadlock Graph
• Query – Wait Stats
• Query – What’s going on: Collection of parameter combinations for sp_whoisactive, and some simple queries to find/remove -2 spids, list of sessions with locks on a given database etc.
• Query – Where is my backup: Also includes templates for taking a backup and doing a restore (with MOVE).
• sp_BlitzIndex
• sp_BlitzFirst
• sp_DBPermissions
• sp_SrvPermissions
• sp_RestoreGene
• sp_WhoIsActive

 
Now obviously for the scripts that I’m getting from other people that are being constantly updated I pull them down and replace them every now and again. Others that I write I’ll make changes to as I think of something. I’d like to think why I have most of these is pretty self explanatory. Basically, if I have to do something twice and there might be a third time I’m going to find a script for it. If I don’t find one I’m comfortable with right then I do my best to write my own. Oh, and once I’ve used the script for a while I clean it up and blog it. (In case you hadn’t noticed.)

And last but not least, some of these (the What’s going on & Backup scripts) that I use that most often I pin to a separate tab row.