Patrick Keisler

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.

May 282019

The other day I was asked why Management Studio limits you to only 200 rows when you right click on a table to edit the data.

The easy answer is because that is how Management Studio is configured. However, it’s also something that can easily be changed. From the menu just select Tools and then Options.

In the Options window, select SQL Server Object Explorer on the left side. On the right side you’ll see the the Table and View Options. Change the Value for Edit Top <n> Rows to something like 500 and then click OK.

Now go back and right click on your table. You will now see Edit Top 500 Rows.

An alternative to changing the configuration value is to click the SQL button on the toolbar.

This opens a query window which will then allow you to edit the TOP (n) value. Once you change the value, then click the Execute button on the tool bar.

You can also rewrite the query to filter out more of the rows before you edit them. Just follow the same steps above; edit the query and then click the Execute button on the toolbar.

Finally, in the same Options window, you can change the value for the Select Top <n> Rows.

This will change the same right click menu except for the the select.


May 142019

There is still no love for SQL Server Management Studio. SSMS v18 Beta included a Dark Theme, but somehow that did not make it into the final release of the product. Other apps have this capability (Visual Studio, Visual Studio Code, Chrome, Edge) but not SSMS. If you are like me, sometimes you want to switch your apps to a dark theme for added flair. A quick web search will reveal several ways to hack SSMS to display a dark theme, but that may not produce the best results. So, until Microsoft decides to include it, here is how I have been customizing SSMS for the last several years.



These colors may not work for everyone, but it does demonstrate how you can customize SSMS to fit your tastes.

To get started, from the menu select Tools and then Options.

In the Options window we’ll need to select Environment and the Fonts and Colors.

For Show settings for, select Text Editor and change the font size to 12.

Under Display items, select Plain Text and then click the Custom button next Item foreground.

In the Color window, change the Red to 228, Green to 228, Blue to 228, and then click OK.

Under Item background, select Black from the dropdown list.

Follow the same steps to change each of these other items below.

Text Editor:

  1. Font size: 12

Display Items:

  1. Plain Text
    1. Foreground: 228,228,228
    2. Background: Black
  2. Selected Text
    1. Background: Yellow
  3. Line Number
    1. Foreground: 255, 255, 128
  4. Comment
    1. Foreground: 255, 175, 45
  5. Keyword
    1. Foreground: 127, 176, 228
  6. SQL Operator
    1. Foreground: 199, 199, 199
  7. SQL Stored Procedure
    1. Foreground: 128, 255, 128
  8. SQL String
    1. Foreground: Yellow
  9. SQL System Function
    1. Foreground: 220, 120, 255
  10. SQL System Table
    1. Foreground: 128, 255, 128
  11. String
    1. Foreground: Yellow
  12. URL Hyperlink
    1. Foreground: Cyan

Next, select Grid Results from the Show Settings For dropdown list. Change the font to Microsoft San Serif and the size to 12.

Then select Text Results from the Show Settings For dropdown list and change the font to Consolas and the font size to 12.

Changing the Grid or Text Results will require you to close and reopen SSMS for the changes to take effect. The last thing is to display the line numbers. On the left side of the Options window, select Text Editor, Transact-SQL, and then General. Click the check box next to Line numbers and click OK.

Once you have the colors you want, then go to Tools and click Import and Export Settings.

This wizard allows you to save all the customization settings to a settings file. The file can be used as a backup, or you can use that file to import to SSMS running on another workstation.

Happy customizing!

Jul 172018

This article assumes you already have a basic understanding of SQL Server Audit, but if not, use this link to catch up on all the details.

Are you required to have xp_cmdshell enabled on one of your servers? If so, then setup a SQL Audit now to track its use. Never mind the implications of enabling xp_cmdshell, as a DBA you are responsible for what happens on your servers and tracking the use of xp_cmdshell should be a priority.

The first step is to create a server audit to hold the events that we collect.

USE master;
CREATE SERVER AUDIT Audit_xp_cmdshell
WHERE (object_name = 'xp_cmdshell');

You’ll notice that we added a WHERE clause that instructs the audit to only collect events that reference the object xp_cmdshell. All other events will be ignored.

Next, we need to create a server audit specification using the
SCHEMA_OBJECT_ACCESS_GROUP. This server-level action group is triggered when a permission is used to access an object such as xp_cmdshell.

FOR SERVER AUDIT Audit_xp_cmdshell

Running the following commands will make sure both the audit and audit specification are enabled.


To test our audit, we need to make sure xp_cmdshell is enabled.

EXEC sp_configure 'show advanced options',1;
EXEC sp_configure 'xp_cmdshell',1;

Then call xp_cmdshell to create some activity.

EXEC xp_cmdshell 'DIR E:\SQL2017\SQL_Tempdb*.* /b';

Viewing the audit log, you can clearly see the command that was executed and the login that called it.

As we have seen, if you have a server that has xp_cmdshell enabled, then using SQL Audit can help you keep track of the commands that have been executed.

Everything we have covered here will work in all editions of SQL Server 2012 and above.

Jan 022018

Every now and again as a Microsoft PFE, you get a chance to make a big difference for a customer. One such occasion happened just recently when I was asked to help find a way to automate the daily checks the DBA had to perform every morning. The result was a PowerShell script that reduced that manual task down from an hour to less than a minute.

You can read the full article here on MSDN.

The PowerShell script can be downloaded from here.