Press "Enter" to skip to content

Category: Powershell

Finding Bad (Worse?) NOLOCK Statements across Instances

Published 2021-08-04 by Kevin Feasel

Aaron Bertrand powers up for about six episodes straight, but the results are amazing:

In Part 1 of this series, I showed how to use a Visitor pattern to walk through one or more T-SQL statements to identify a problematic pattern where NOLOCK hints are applied to the target of an update or delete. The method in my initial examples was very manual, though, and won’t scale if this problem might be widespread. We need to be able to automate collecting a potentially large number of statements across an entire environment, and then try to eliminate false positives without manual intervention.

Read on to see how you can take what Aaron wrote last time and make it scalable.

Comments closed

Estimating Nonclustered Index Sizes with Powershell

Published 2021-08-03 by Kevin Feasel

Alex Stuart performs some calculations:

I recently encountered a requirement to estimate the size of (a lot of) nonclustered indexes on some very large tables due to not having a test box to create them on or the time to build one. I couldn’t find a script to do this, and as any programmer knows, laziness is the real mother of invention, so I wrote one.
This post summarises how we can calculate this and provides a Powershell function to do so.

I used Microsoft’s documentation as a basis on how to do it, but as it’s a wall of text that’s a little tricky to follow, I’ll go over the basics here. I’m only covering the leaf levels and non-MAX columns that would create LOB pages – I’ll explain why later.

Click through for the article and to see how Alex’s calculations play out.

Comments closed

Fun with Arrays in Powershell

Published 2021-07-30 by Kevin Feasel

Robert Cain looks at how arrays operate in Powershell:

In this article, we’ll look at the different ways to use Arrays in PowerShell. We’ll cover the basics, then move onto more advanced topics.

For all of the examples, we’ll display the code, then under it the result of our code. In this article I’ll be using PowerShell Core, 7.1.3, and VSCode. The examples should work in PowerShell 5.1 in the PowerShell IDE, although they’ve not been tested there.

Click through for a variety of tips and tricks when working with arrays.

Comments closed

Remote Powershell Management via the ISE

Published 2021-07-23 by Kevin Feasel

Jeffrey Hicks has a use for the Powershell ISE:

Way back before the days of PowerShell Core, or even VS Code for that matter, the PowerShell ISE was the center of my PowerShell world. I spent a lot of time finding ways to make it easier for me to use and to push it to its limits. Naturally, the PowerShell ISE doesn’t play much of a role for me these days. But that may not be true for you. In fact, one area where the ISE can be useful is with remote sessions. 

Read on to see how.

Comments closed

String Formatting in Powershell

Published 2021-07-21 by Kevin Feasel

Robert Cain continues a series on fun with Powershell and strings:

Specifically, this can control the output when we embed a numeric value inside a string. Passing in special formatting instructions will make it easy to display values with commas, as currency, or even as hexidecimal.

For all of the examples, we’ll display the code, then under it the result of our code. In this article I’ll be using PowerShell Core, 7.1.3, and VSCode. The examples should work in PowerShell 5.1 in the PowerShell IDE, although they’ve not been tested there.

Robert has quite a few examples, so check them out.

Comments closed

Log Shipping Configuration with Powershell

Published 2021-07-20 by Kevin Feasel

Lee Markum needs to do log shipping in bulk:

I recently needed to configure log shipping for multiple databases at once as part of a migration project. I turned to PowerShell to do this.

But before we get to that part, this post assumes that you’ve done the upfront work to create shares for the backups to write to and for the backups to be copied to. This will involve providing the right permissions for the SQL Server service accounts involved in Log Shipping. If you are not familiar with this, that’s perfectly fine. Check out this article in MS Docs first.

Click through for the next steps in the process, including a well-timed Get-Help call.

Comments closed

Identify SQL Server Configuration Drift

Published 2021-07-14 by Kevin Feasel

Garry Bargsley won’t let the cattle become pets:

As you sit and wonder about when the next Star Wars movie is going to come out, do you ever get the thought of “I wonder if all my SQL Servers are configured the same?”

Occasionally, I get a thought like that run through my mind. Or I might see something on Twitter or Blog post about something, and it sparks the question.  Not about Star Wars, but my SQL Server environment.

Today something triggered me to confirm that all of my SQL Servers had the default backup compression setting set to enabled.

Garry mentions dbachecks at the end, and it’s a really good way of performing a fairly large number of such checks easily.

Comments closed

Filtering Unique Objects in Powershell

Published 2021-07-14 by Kevin Feasel

Jeffrey Hicks solves an interesting problem:

A few weeks ago my friend, Gladys Kravitz, was lamenting about a challenge related to filtering for unique objects. PowerShell has a Get-Unique cmdlet, and Select-Object has a -Unique parameter, but these options are limited. On one hand, I’d say most things we manage with PowerShell are guaranteed to be unique. Objects might have a GUID , ID, or SID, to guarantee uniqueness. But, and this is Gladys’ situation, sometimes the things we are managing come from an external source. Such as importing data from a CSV file. In this situation, it is definitely possible to have duplicate objects.

This turns out to be a bit harder than expected.

Comments closed

Fun with Powershell Strings

Published 2021-07-13 by Kevin Feasel

Robert Cain shows off some features around strings in Powershell:

PowerShell has some of the best, most flexible string handling of any language I’ve used. In this post we’ll look at some string basics, then some of the features that make it special.

Note in these demos I’m using PowerShell Core 7.1.3 and VSCode. Everything here should also work in the PowerShell 5.1 and the PowerShell IDE.

Read on for types of strings, embedded quotations, string interpolation, and more.

Comments closed