Patrick Keisler

Jul 282020

In my last Management Studio tip, I demonstrated how you can drag and drop column names to a query window. The only part I do not like about that is that all the column names are on a single line. I prefer to have on column name per line, and to quickly fix this we are going to use a Regular Expression (RegEx) within the Find and Replace dialog box.

For those not familiar with RegEx, it allows you to search through text using a multitude of options. In our example, we are going to use RegEx to quickly find and replace text in a script.

Below we have a single select statement that has all column names on a single line. We want to get each column name on a separate line and with the comma at the beginning of the column name.

Press Ctrl+H to open the Find and Replace dialog box. Then we need to click the RegEx icon.

In the Find box we want to find the comma and the space that follows it: “, “.

In the Replace box we want to enter “\r\t,”.

\r means we want to insert a carriage return.

\t means we want to insert a tab.

And follow that with a comma. Click the Replace All button.

Now we should have each column name on a separate line.

Next, we will use RegEx to get rid of the square brackets around the column names.

In the Find box we want to enter “\[|\]”.

\[ means we want to look for the left square bracket. The backslash is used to escape the left bracket since it is a RegEx special character.

| (vertical pipe) is used for an OR statement (i.e. we want to find either the left or right bracket).

\] means we want to look for the right square bracket. Once again, the backslash is used to escape the special character.

We want to leave the Replace box empty. Click the Replace All button.

Now we should see that our script is formatted in a way that is much easier to read.

These were simple examples using a small script, but RegEx can be an incredibly powerful when you need to search (and replace) text in a script that may be thousands of lines long.

Additional Reference:

Jul 072020

Continuing on my recent trend of Management Studio tips, here is another that could save you a few minutes.

In today’s scenario, we need to write a select statement that returns only the first few columns of a table. It is a super simple statement, but instead of typing out all the column names, we can just drag and drop them from the Object Explorer.

Watch the video below for a quick demo.


If you are not a fan of the square brackets around each object name, then you can change the settings so they no longer appear.

From the menu, select Tools and then Options.

Select the SQL Server Object Explorer tab and then change the “Surround object names with brackets when dragged” to false. Click OK to save the changes.

It takes effect once you open a new query window.


Note: This setting only works for drag and drop. Generated scripts will still use the square brackets.


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.

May 262020

What happens if you have a SQL Agent job, but the logic is not aware (or cannot be aware) that you may have an Availability Group in place. There is nothing built-in to Agent to account for this, but there is a way can you configure an Agent job to check for the existence of an AG before proceeding.

Here is the scenario.

First, we have a 2-node cluster running a synchronous Availability Group with automatic failover and readable secondaries turned off. Next, we have a requirement to run a SQL Agent job every hour to collect data from a user database. Since the AG is configured for automatic failover, we need to make sure the job will continue to run successfully regardless of which node is active.

With readable secondaries turned off, this creates a problem for any Agent job that needs to read from those user databases. Those jobs will fail with an error saying the database not accessible for queries.

We can easily get around this issue by using a single query and an extra job step.

First, we need to add a TSQL job step that we will call “AG Check” to execute the following query.

IF sys.fn_hadr_is_primary_replica('WSS_Content') <> 1
  THROW 50000, 'This is not the primary replica.', 1;

This query uses a DMF, sys.fn_hadr_is_primary_replica, to determine if a database is a part of the primary or secondary replica. The function will return a value of 1 if we are on the primary replica, so if it returns anything else then we want to throw an error. The error will allow us to change the behavior of the job.

Note: sys.fn_hadr_is_primary_replica was introduced in SQL Server 2014; however, I wrote a function that can do the same thing for SQL Server 2012.

Next, on the Advanced tab, we need to change the On Failure Action to “Quit the job reporting success”, then click OK.

We want to use “Quit the job reporting success” in stead of “Quit the job reporting failure”, so the overall result of the job will be successful. That way we will not cause additional email alerts to be sent.

Finally, on the Steps tab, we need to use the Move arrows to make sure AG Check is the first step on the list, and make sure it is listed as the start step.

The result is we that we now have a job with two steps. The first step checks the status of the replica. If it succeeds, then we can proceed to the next step to do actual work. If the first step fails, then we gracefully exit the job.

There are other ways you can setup an Agent job to account for an Availability group, but this method is quick and easy to setup for almost any environment.