Tag: SQL

This is the first PowerShell script that I wrote. I was inspired to start using PowerShell after I met with one of our developers. He creates scripts to automate repetitive​​ administrative tasks (e.g. daily system health check). I asked the developer if he learned writing PowerShell from a book or a training class. ​​ He said that he neither read any book nor attend a PowerShell training class. Instead, he told me that he just knows what he wanted to achieve and then he searched the internet on how to achieve it. In other words, he just Googled the codes.

I always wanted to learn PowerShell. ​​ As a DBA, I think it would be beneficial to use this powerful tool. I read books about​​ PowerShell and did the exercises but I feel I never progressed and I’m still not able to create anything.​​ 

So I tried to do what the developer told me. First, I started to think what I want to achieve and that is to send disk space usage of our Database Servers to my email. ​​ This is so that I won’t have to login to our DB servers everytime.

Below is the PowerShell script.

#.SYNOPSIS

# Sends Daily Disk Space Usage Report

#

#EXAMPLE

#.\Send-DiskSpaceUsageReport.ps1

#

$smtpServer​​ =​​ "Fsmtp.our.property.com.sg"

$smtpFrom​​ =​​ "NXPOPARVRAPPLE@nhg.local"

$smtpTo​​ =​​ "valenciajec@ncs.com.sg"

$messageSubject​​ =​​ "OPAS TTSH Daily Disk Space Usage Report"

[string]$messagebody​​ =​​ ""

$logs​​ =​​ Get-Content​​ D:\DBA\DB_Health_Checks_Node1\6.DiskUsage_Node1_/TTSH.txt,​​ \\10.240.xx.xx\d$\DBA\DB_Health_Checks_Node2\2.DiskUsage_Node2_TTSH.txt

foreach​​ ($log​​ in​​ $logs​​ )

{

 ​​ ​​ ​​​​ $messagebody​​ =​​ $messagebody​​ +​​ $log​​ +​​ "`r`n"

}

$smtp​​ =​​ New-Object​​ Net.Mail.SmtpClient($smtpServer)

$smtp.Send($smtpFrom,$smtpTo,$messagesubject,$messagebody)​​ 

This script will add a message body to emails sent from script. I have a SQL Server Agent job that generates the text file that contains the disk space usage report. So basically, the PowerShell script will get the contents of the text​​ file and add it to the message body of the email.

The next step is to​​ create a Scheduled Task​​ to run the PowerShell script.

• Open Task Scheduler

Open Task Scheduler and Create a new task. Name it and set your security options.  Check "Run with highest​​ privileges" as our scripts need to run as admin. Choose “Run whether user is logged on or not.”

• Set Triggers

• Create your Action

Click on Actions tab and click New.

Action: Start a program

Program /script: PowerShell.exe

You don’t need to​​ put a path as it should already be on your system

• Set Argument

You first need to set the​​ ExecutionPolicy. There are two options to do that either you set the ExecutionPolicy on the machine or on a per-script basis. I will only set it on a per-script basis but If you want to set the execution policy globally, you can issue this command from within PowerShell.

Set-ExecutionPolicy Unrestricted

To set the ExecutionPolicy on a​​ per script basis, put the line below on the Add arguments (change the file name to​​ your script’s file name.ps1)

 ​​ ​​ ​​ ​​ ​​ ​​​​ -ExecutionPolicy Bypass -File D:\DBA\Scripts\PowerShell\Send-DiskSpaceUsageReport.ps1

  Indicate the path of your script in the Start in(optional)

• Change Settings

I encountered the error “Launch request​​ ignored, instance already running (154257)”and the task failed to run. ​​ To resolve this issue, I changed the setting to “Stop the existing instance” ​​ 

• Save and Test