Jun 232020

Have you ever had a need to edit multiple lines in a T-SQL script? As in, type the same text repeatedly on multiple lines? Well, a little-known key stroke makes this an easy task in Management Studio.

Simply place your cursor where you want to start typing, hold down Alt+Shift and then hit either the Up or Down arrow key. You will then see a vertical blinking cursor. Now just start typing. You can even hit Enter which will add a new line in between each one where you can continue typing.

Watch the video below for a quick demo.

Jun 092020

A while back I wrote an article about how I created a PowerShell script for a customer to help them with their morning routine of checking the health of each SQL Server in their environment. I have not worked with that customer in a good while, but I thought it would be useful to update the script with some additional checks.

The original script helps the DBA answer these questions:

  1. What is the uptime of each SQL Server?
  2. What is the status of each database?
  3. What is the status of each Availability Group?
  4. What is the backup status of each database?
  5. What is the available disk space?
  6. Are there any SQL Agent failed jobs in the last 24 hours?
  7. What errors appeared in the SQL errorlog in the last 24 hours?

For the updated script, I have added checks to answer these questions:

  1. What is the status of each SQL Server service?
  2. What is the status of each Windows Cluster node?

The first check will simply query the sys.dm_server_services DMV to get the status of each service. If the startup mode is “Automatic” and the service is not “Running” then it results in a critical status; otherwise, the status is good.

The second check will query both the sys.dm_os_cluster_nodes and sys.dm_hadr_cluster_members DMVs to get the status of each cluster node. If a node is “Up”, then the status is good; otherwise, it reports a critical status.

The final changes were to fix a few bugs and other functional issues.

  1. Changed the error trapping to continue processing even if a SQL Server is unresponsive.
  2. Updated the SQL Agent jobs check to ignore disabled jobs.
  3. Updated the database status check to account for mirrored databases.

The updated script can be downloaded from GitHub.

The link below is the original article with a complete rundown of how it works.