July 10, 2018 by Kenneth Fisher
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.
- 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.
- 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).
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.