Patrick Keisler

Dec 292020
 

On January 15, 2021, PASS will cease to exist.

https://www.pass.org/PASS-Blog/ArticleID/888/ArtMID/99177/preview/true

I cannot tell you how much this disappoints me. PASS has been around for my entire database career, and now it is gone. I would not be where I am today without PASS. PASS events helped see what I was missing while siloed at my previous company. It took me about 10 years before I really got involved with PASS, and once I did, I was hooked. I could not believe how many people wanted to share their SQL experiences with the world. As a result, I too wanted to share my knowledge with the community. That is why I started this blog and started presenting at various events. The knowledge I gained from attending those events helped me become a better DBA. However, the networking opportunities were always worth more to me than any presentation. Do not get me wrong, the presentations were spot on, but the friendships I formed will last forever.

The SQL community will forge ahead, just in a different form. There are already lots of other organizations that host SQL events around the world. And if you have trouble finding one, just ask the community. We are always ready to share information.

See you all at the next SQL event!

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:
https://docs.microsoft.com/en-us/sql/ssms/scripting/search-text-with-regular-expressions?view=sql-server-ver15

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.
https://github.com/PatrickKeisler/SQLMorningHealthChecks

The link below is the original article with a complete rundown of how it works.
https://docs.microsoft.com/en-us/archive/blogs/samlester/sql-server-dba-morning-health-checks