SQL long running jobs alert script in PowerShell

databaseHi folks!

Recently there was in a need of monitoring jobs on SQL – if they finish in timely manner. So I was looking for ready solution, found two:

https://devinknightsql.com/2012/07/07/identifying-long-running-sql-agent-jobs-script/ and http://www.sqlchicken.com/2012/07/identify-and-alert-for-long-running-agent-jobs/ however both are SQL jobs that monitor SQL jobs. I feel quite good in TSQL…

1fo1ju

…however that solution didn’t suit me from few reasons:

  1. I wanted to do monitoring of SQL jobs as an independent from SQL jobs failure.
  2. I might need to modify add something in the future and it would be easier for me in PowerShell
  3. Above solutions were giving alerts when a job run time was above average – not over certain time – an I needed that. Didn’t know how to do it in SQL.

So the decision was made:

b9c

To make it running in your environment just modify these lines:

## 10th-12th LINE
## SPECIFYING DATA FOR SENDMAIL FUNCTION
$fromemail = 'Job_Alert@domain_name.com'
$mailserver = 'mail_server.domain_name.com'
$toemail = 'recipient@domain_name.com'
...
## 21st LINE
## SPECIFYING DATABASE CONNECTION STRING
$dataSource = “DATABASE_SERVER_NAME”
...
## 71st LINE
## BELOW SPECIFY HOW MANY MINUTES IS THE TRIGGER - 5 IN THIS EXAMPLE
if ($Date_Diff.TotalMinutes -gt 5){

So in this example a script will send an email in HTLM:

przechwytywanie
If a job runs over 5 minutes.

So there you go 🙂 it is easy, no parameters and really dirty. I like it 🙂

Script can be found on TechNet and GitHub.

Advertisements

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