T-SQL Tuesday #130 – Automation Helpers

Introduction

This month’s T-SQL Tuesday is being hosted by Elizabeth Noble. The subject is “automate your stress away”. As Elizabeth describes it: “My invitation to you is I want to know what you have automated to make your life easier? “. This post will be my contribution for T-SQL Tuesday #130 – Automation Helpers.

Once again, this is a pretty wide area, that gives everyone plenty of possibilities for topics. This is good, since I think it will encourage more people to actually write a post. Just writing a useful blog post, and getting quality links back and forth from other related blogs in the community is good for everyone who participates!

In my case, I am going to discuss a few scripts that I have developed and published that are automation helpers.

T-SQL Tuesday #130 – Automation Helpers

Over the years, I have developed several useful scripts for common tasks that save me quite a bit of time during deployment and configuration tasks.

SQL Server Diagnostic Information Queries

As the only DBA at NewsGator, I eventually got tired of hearing “What’s wrong with the database?” every time there was an application slowdown. Because of this, I started putting together a series of diagnostic queries that would help me to understand what, if anything was wrong with the database.

My queries are useful in a crisis situation and on a proactive basis. Many people have written code to automate running these queries and collecting the results. One great example is the Invoke-DbaDiagnosticQuery method from dbatools.io

These queries have been very useful to me, and many other people around the world over the years. My latest post about these queries is SQL Server Diagnostic Information Queries for September 2020. The latest versions of these queries are always available here.

Juypter Notebooks

More recently, I have written Juypter Notebook versions of my SQL Server Diagnostic Information Queries. This makes my diagnostic queries available cross-platform, to Azure Data Studio users.

These are hand written and formatted versions (with some formatting help from Julie Koesmarno) of my diagnostic queries that I think are better for end users than automated conversions of my T-SQL versions. The latest versions of these Notebooks are always available here.

SQL Server Agent Alerts for Critical Errors

I also have a script that creates SQL Server Agent Alerts for thirteen different SQL Server and hardware related errors. As long as you have a SQL Operator defined (that matches the operator name in the script), then my script will create those alerts using the name of your instance as part of the alert name. This shows what the alerts will look like.

I wrote about this in some more detail in my post, SQL Server Agent Alerts for Critical Errors. I also have a YouTube video that explains a diagnostic query for checking your SQL Server Agent alert information.

Final Words

I hope that these scripts and queries have been useful and saved you some time! I also want to thank Elizabeth for hosting this month.

If you have any questions about this post, please ask me here in the comments or on Twitter. I am pretty active on Twitter as GlennAlanBerry. Thanks for reading!