Email Alerts with Performance Monitor


Not everything must be revolutionary, some great tips to better manage your systems are a simple matter of thinking in small easy evolutionary steps.

A reality check

Having said that, I have been thinking about what types of contributions I can make, what things I can share with the SQL Server community that can be useful today, right now. As exciting as the STRING_AGG function in vNext or the CREATE OR ALTER statement in SQL Server 2016 SP1 are, I’m pretty sure blogging about those doesn’t fall into a category of really helpful contributions. On one side, lots of people are writing about them already and on the other hand, I don’t think you will go nuts and start re-writing your T-SQL code to incorporate new T-SQL functions that are likely not going to be retrofitted into earlier incarnations of our beloved server.

So, what useful tips and tricks can I share? What things are there that I can write RIGHT NOW that you can use RIGHT AWAY. Read on my friends, we are going to talk about alerting and hard drive space today.

Hard drive space alerts in your inbox

This one is meant to be used by everyone, literally. Not only because this technique relies on features that have been here for ages, this also will help all of you because is built upon features that are provided out-of-the-box by every Windows Server. Is not a radical idea, it is just a useful tip that will help many of you, humble DBA’s working on a tight budget, to have a better control and management on hard drive availability, with ingredients you can find in your pantry: Task Scheduler, Performance Monitor and a bit of freeware console apps.

If you want to get alerts when your server is hard-drive-hungry, follow these steps. An automated solution like this is very simple to implement and is going to cost you nothing. The beauty of this technique is that is very flexible so you can use it for any Performance Monitor counters, so you can think of this as a generic “push” notification system of alerts for any system indicators, just change the Logical Hard Drive object with any object of your preference and the threshold of notification with any threshold that makes sense to you and voila!

MailSend and the initial PerfMon Setup

First, let’s start with the email send part of the recipe. I suggest you get a copy of MailSend. Place Mailsend anywhere in a folder where your SQL Server Service account has permissions to read. Also place a .cmd file in the same folder that reads like this (I called mine SendWarningEmail.cmd)

“C:\[your_path_here]\mailsend1.16.exe” -smtp [your_smtp_server] -port [your_port] -to [your_email_address] -from [your_email_sender] -starttls -auth -user [your_username] -pass [your_password] -sub “[your_subject] -M %1

You may have to play a littlebit with MailSend parameters (such as starttls and auth) based on your SMTP security settings, but all in all it should be very simple.

In Performance Monitor create an Alert (a type of Data Collector Set) with the counters you want to alert and their thresholds. As an alternative of creating the alert manually, you can create an alert based on a Template and follow this hyperlink to use this template instead.

If you use the template you can skip the steps in the “Alert Configuration” section below, but remember to change the drive letters to the ones you have in your own server.

Alert Configuration

Here are the important screen tabs

perfmon_settings

Fig1 – Main page of the Performance Monitor with the Hard Drive Space Alert pefmon alert created.

perfmon_settings_2

Fig2 – Alert Properties “Schedule” tab with a new schedule (Everyday, starting at 12:00AM)

perfmon_settings_3

Fig3 – Data Collector Set Properties “Alert Task” tab with a call to a Scheduled Task (diskalert)

Two critical things in the previous tab.

  1. The “Run this task when an alert is triggered” must contain EXACTLY the same name as the task we will create later on
  2. The “Task arguments” is where you save the body of your email. In this case I used some of the alert arguments and literal text, the exact value in the text box in this case is
"Server's Free Disk Space is low. Counter {counter} 
reports {value}% free space, which is less than the threshold 
of {threshold}%. Please check this server."

(Use this as an example for your alert Task Arguments)

Initial Scheduled Task Setup

Create a Scheduled Task in Task Scheduler using the “Create Task…” action. Alternatively, you can use the “Import Task…” option to save yourself from performing all the following steps associated with the manual creation of a task (use the template diskalert.xml here). If you use the template, just make sure you update two things in the xml:

  1. The path to point to the script this task will execute.
  2. The usernames for the <Author> and <UserID>

If you use the template you can skip the steps in the “Scheduled Task Configuration” section below.

Scheduled Task Configuration

  • The only thing you have to do is to make sure the name of the task matches the value you entered previously in the alert setting “Run this task when an alert is triggered”.
  • Enter a description, such as: This is the task that will be triggered by the alert Hard Drive Space Alert running on Performance Monitor if one of the partition’s Free Space is below certain threshold.
  • On the Security Option Box, select “Run whether user is logged on or not” and also check the checkbox “Do not store password. The task will only have access to local computer resources”
  • In the Actions Tab this is what you need

 

scheduled_task_property

Fig4 – Scheduled Task Actions Tab (new action)

Here you have to provide a path to the Program/script you are going to execute, for this case (sending the email) you want to provide the path to SendWarningEmail.cmd batch

The important thing is to make sure the arguments you passed from the Alert are added to the program to fire the email, for that purpose we enter $(Arg0) in the Add arguments (optional) box

In Closing

If you have followed this blog post to the end and repeated this steps in your system, you have now a zero-cost, beautiful alerting system that you can easily tweak. You can change the percentage at which you want to be notified, how often the alert is triggered and you can even use this same technique to check and report on any Performance Monitor counter you are following closely.

Do you use the out-of-the-box tools provided by Winsdows/SQL in an interesting way that other users can benefit from? Feel free to share with us on the comment section or look for my contact info in this site and let me know so we can share with the community.

Have a great day!

 

Advertisements

About Martin Surasky

Full-time time daddy, part time DBA, and guitar player (only if I'm not administering a databases or with my daughter) and part time Coffee Crisp addict (thanks Nestle)...
This entry was posted in Simple Tricks and How To's. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s