The Multifunctioning DBA

Oct 30 2011   10:00AM GMT

PowerShell SQL Server Health Check Script – Revisited

Colin Smith Colin Smith Profile: Colin Smith

In the past couple months I have received a few comments and questions about my SQL Server Health Check Script. So I thought it might be a good time to revisit the script and clear some things up and give some other options as well. This will be done in a series of posts so that we can dive more deeply into the script.

This script is still good and does still work if you set it up properly, but the script was written early in my Powershell career and i have made a couple modifications over time that may help you out and I can clear up some of the confusion about the sqltab.txt file.

Lets start off by attempting to clear up some of the confusion about the sqltab.txt file. This is the file that is used as an input for the script. The script reads this file in, using the import-csv cmdlet, and the script is able to set up all the variables that you need for the input. This file must be comma delimited and can store anything you want in it. So mine looked like the following:

monitor,Server,Instance,TorP,ErrorLog,Ping

Those were the headers in my file but you can have any headers that you need to make the script work for you the way you want it. I used this file for many scripts but for just the health check you really only need the instancename. In my script, since I have named and default instances, I broke that into ‘Server’ and ‘Instance’. This allowed me have the script determine the instance name. The other headers are up to you, just use headers that you think you might need and you can always add or subtract things from this file.

As for the location of the file, it really does not matter. Some people were confused about the location I had mine.

$servers = Import-Csv “\\ent-pocpacapcx01d$\monitoring\sqltab.txt”

I just had the script getting to the file with a UNC and that was for portability. I could run the script from anywhere on my network and it would find that one file and run. The location, name, and contents of the file are up to you. You may have to modify the script a bit if you modify the file headers and content and you will have to change the location of the file for sure. That is easy to do though.

Now that we have talked about the input file and the things that you can do to change the input file lets go over where in the script you will have to make changes if you make changes to the input file.

You have to change the location of the input file and that is done with the following line.

$servers = Import-Csv “\\ent-pocpacapcx01d$\monitoring\sqltab.txt”

Just make this where you put the file and whatever name you gave your file. In powerwhell if you run the following you will be able to verify that you are able to read the file in and that the values are being set up the way you need them.

$servers = import-csv “yourfilepath\yourfilename.txt”

$servers

Then in the console window you should see all your servers being listed out on the screen with whatever headers you put in the file. Like this:

PS C:\Documents and Settings\cosmith> $servers = import-csv “c:\input\sqltab.txt”
PS C:\Documents and Settings\cosmith> $servers

server                           instance                         TORP                           monitor
——                        ——–                      —-                          ——-
server1                         tst                                 Test                                 Y
Server2                         PRD                               Prod                                 Y
Server3                         Default                         Prod                                  N

Now that you have verified that the script is going to be able to read the file in correctly, you will need to modify the script to set up the variables so that they are easy to use.

So that is all about the input file and how to use the input file. More on this in a few days.

Hope that this helps.





1  Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Jackandjill
    hi i have followed the previous blog post and trying to figure out how to get this work but unable to get it to work , and trying to follow you on twitter but it says the username doesnot exist anymore is there any way to contact you? I will post a stackoverflow link to the question i posted there.. my twitter is https://twitter.com/#!/bharathyp
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: