For this month’s T-SQL Tuesday, John McCormack (blog|twitter) asks us: “What are your go-to short, handy scripts”? I’ve got so many handy dandy scripts, it was hard to choose.
Where to start?
I have so many go-to scripts that are short, amazing, and I use all the time. I’ve been using SQL Server since the turn of the century, and over the last two decades, I’ve found and written a lot of helpful things. And I have a terrible memory, so even if it’s just a few lines, I often keep it bookmarked or saved for when I need it. As an aside–remember that having a folder of utility scripts isn’t a sign of weakness or incompetence. If anything, it’s the opposite–those utility scripts are basically all battle scars for the database battles you’ve fought over your career. As we become senior, we just fine-tune our collection. With seniority, you don’t stop having that collection of scripts–you just get better at curating them, at making them generic & reusable.
Back to the question at hand
Some of my favorite short scripts are actually templates or code samples from other folks. These templates help me make sure I write code the optimal way–either to make sure it performs well, or that I get correct results, or maybe just to help tickle my memory on what various parameters actually do. For nearly a decade, every time I write a cursor I’ve opened up Aaron Bertrand’s (blog|twitter) blog post on what impact different cursor options have. I never remember that I want to declare my cursor as LOCAL FAST_FORWARD, and while I’m on that page I grab Aaron’s example code to use as my template with all the declaration, looping, and fetching taken care of. Thanks to this sample, it’s been a very long time since I checked the wrong @@FETCH_STATUS or sent a cursor into an infinite loop by forgetting to FETCH inside my loop.
No offense to Aaron, but I can’t exactly admit that I love cursors in public. Not if I want to escape without having to fight another DBA. So let’s pretend I didn’t mention that.
My DBA database
I’ve written about my DBA Database quite a few times. Some of the code in that database is written recently, and some of it was originally written a decade ago. Regardless of how old it is, or how good of a DBA I was when I wrote it, these little scripts are life-savers, and they are always at my fingertips.
Why put it in a database?
I spend most of my day, and most of my troubleshooting, in SSMS. When Production is down, and it feels like I am the This Is Fine dog, SSMS is my coffee cup. To me, that means having my favorite scripts in a database, on every server, all the time. Period. I never have to copy a script from my C:\ drive, or clone a git repo in the heat of battle. I don’t have to worry about whether I’ve accidentally butchered the file the last time I had it open.
Why open source?
For one, I can easily share my scripts. If you find my code helpful, you can use it! Download it and deploy it at your company. You’re free to submit PRs for your own contributions, submit issues, etc.
I’ll admit there’s also a selfish aspect of it, too. Employment agreements aren’t always kind to employees. Sometimes they are draconian and anti-employee in the way they attempt to protect the company. I don’t agree with that stance (and thankfully my current employer isn’t like that). However, that’s why having your utility code on GitHub (or similar) is important–the git history acts as “receipts” for your utility code being “prior art” that predates your employment.
SHOW ME THE DAMN CODE ANDY!!!
Ope. Sorry. I’m easily distracted. That was a long tangent.
Here are 10 of my favorite short scripts, with a description of what they do. All of these handy-dandy scripts use my DBA database.:
- Check used vs available disk space on all available drives where SQL Server has data files:
EXEC DBA.dbo.Check_DriveSpace;
- The D:\ drive is low on space! Show me file size information for all data files on the D:\ drive:
EXEC DBA.dbo.Check_FileSize @Drive = 'D';
- I don’t want to see file info for data files, but show me file info for every log file on the server:
EXEC DBA.dbo.Check_FileSize @IncludeDataFiles = 0, @IncludeLogFiles = 1;
- Check for blocking exceeding 35 seconds
EXEC DBA.dbo.Check_Blocking @BlockingDurationThreshold = 35;
- Check for blocking where at least 5 sessions are being blocked:
EXEC DBA.dbo.Check_Blocking @BlockedSessionThreshold = 5;
- Show me all AG Send & Redo latency on the server:
EXEC DBA.dbo.Check_AgLatency;
- Check if any databases in my AG are reporting an “UNHEALTHY” sync status:
EXEC DBA.dbo.Check_AgLatency @UnhealthOnly = 1;
- Check for transactions open longer than 1 minute
EXEC DBA.dbo.Check_OpenTransactions @DurationThreshold = 1;
- Check for transaction logs with more than 100 VLFs:
EXEC DBA.dbo.Check_LogVLF @Threshold=100;
- Check if the AG containing the
StackOverflowdatabase is Primary or Secondary on this server:
SELECT AGStatus = DBA.dbo.AGDbRole_Get('StackOverflow');
Go grab the code
Head over to my GitHub repo, clone the repo, install my DBA database, and update your build scripts to install it on ever server. Then enjoy the time saved when you can answer your FAQs with one-line queries.