Press "Enter" to skip to content

Category: Powershell

Ending A Powershell Loop

Published 2015-12-04 by Kevin Feasel

Steve Jones shows how to end a loop early:

That’s fine, but it’s not a great loop. It runs 9999 times, which isn’t what I want. It works, but it’s an unnecessary use of resources. However I don’t want to break the loop when the file file isn’t found. There have been issues generating a file, like #350, when #351 exists and is there.

I decided to use a shortcut technique I had learned as a kid. I set a variable and then incremented it when I missed a file. When the increment reaches some value, I break the loop.

I’d just as soon use a break statement, but there are many ways to skin a cat.

Comments closed

SQL Server Startup Parameters

Published 2015-12-02 by Kevin Feasel

Shawn Melton shows us how to modify SQL Server startup parameters using Powershell:

Low and behold the StartupParameters property is one that can be read and set. So how do you set it? Well the one thing to remember is you DO NOT need to remove what is already in that property because IT WILL BREAK YOUR SERVER!

Let me be clear, setting the property means you need to append to what is already there, so don’t just go setting it equal to something like “-T1118”. Doing this will remove the required parameters to start SQL Server itself, and no it will never warn you of this…so proceed at your own risk.

Read the instructions; otherwise, you can mess up your installation, and that’d be a bad thing.

Comments closed

Powershell Service Management

Published 2015-12-02 by Kevin Feasel

Mike Fal gives us a pattern for managing SQL Server services with Powershell, WMI, and SMO:

I have built a function around using the second method that makes handling this process a little easier. Also, because I’m not a fan of passing passwords in plain text, I built the function to take a PSCredential object to keep my account information secure. In order to spare you the wall of text, you can view the full function on my GitHub repository.

The function can be loaded through a variety of methods, but once it is loaded calling it is simply a matter of creating the credential for the service account and calling the function

Good stuff.

Comments closed

Test File Existence Using Powershell

Published 2015-12-01 by Kevin Feasel

Steve Jones shows us the Test-Path cmdlet:

One of the enhancements I wanted to make was check if the file exists, and if not, then download it. However, if it does exist, then I’ll skip the file. I know this means I don’t get updated files if schedules change, which is possible, but in that case, I can just delete the file from my desktop and go from there.

I made a quick search, and found a few links to the Test-Path cmdlet. Essentially you give this a file path and it returns true or false. Almost exactly what I need.

Test-Path is small but helpful, and a vital part of scripts which check files.

Comments closed

PowerShell-Based Health Checks

Published 2015-11-20 by Kevin Feasel

Omid Afzalalghom discusses an open-source tool using PowerShell to read various DMVs and give you a basic health check:

PowerShell is an ideal tool for doing health-checks of a collection of SQL Server instances, and there are several examples around, but few acknowledge the fact that individual DBAs have their own priorities for tests, and need something easily changed to suit circumstances. Omid’s Healthcheck allows tests to be SQL or PowerShell and requires only adding, altering or deleting files in directories.

Grab the tool from his GitHub repo.

Comments closed

Speed Up SQLPS Load Time

Published 2015-11-18 by Kevin Feasel

Shawn Melton shows us how to make SQLPS load a bit faster, and which comes with the obligatory warning:

WARNING: You are modifying the files at your own risk. You have been warned.

If you are not familiar with the files involved with a module, you can read more on that here. The file I found most interesting is the “SqlPsPostScript.PS1” file, located in the SQLPS module folder for the given version of SQL Server:

Check it out.  Those two seconds you save add up over time.

Comments closed

SQL Agent Reporting

Published 2015-11-18 by Kevin Feasel

Mike Fal shows us how to use Powershell and T-SQL to get SQL Agent job status:

This is effective, but I struggle a little with the SQL query. It’s good, but suffers from the structure of the jobs tables in MSDB. We have to account for that and it makes the SQL query a little convoluted. It would be helpful if we could reference a simple data set like the Job Activity Monitor in SSMS.

Of course, this is a leading question on my part. There is a way to do this and it is by leveraging the SQL Server Management Objects (SMO). This .Net library is the API interface for working with SQL Server and is what SSMS is built on. Because it is a .Net library, we can also access it through Powershell.

SMO’s a powerful thing.

Comments closed