Setup Alert New Job created

Question

Post reply

Setup Alert New Job created

rr_littletons

SSC Veteran

Points: 283
More actions
November 30, 2019 at 6:34 pm

#3702238
Disregard this question, a trigger could complete this request, I need a way to be notify when a new job has been created on my SQL Server. Any ideas, I was thinking to write something to monitor inserts on sysjobs table.
- This topic was modified 4 years, 4 months ago by rr_littletons.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

heb1014 Hall of Fame Points: 3790 More actions · Answer 2

I would:

Create a log table
Insert all jobs into it
Create a proc to check for any jobs not in the log table
- if it finds anything, insert the job into the log table and notify
- these must be done within a single transaction
Create a scheduled job to execute the proc

Very simple. You could easily add this process to any server needing to be monitored.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 3

I'd create a morning report for all jobs. It's not difficult to do and will teach you a lot about the tables in MSDB. I render the report in T-SQL as HTML and when a new job appears, its modification data (which is a creation date the first time) is colored in Light Green for a week. That also helps me keep track of if and when a job changed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrey Right there with Babe Points: 780 More actions · Answer 4

rr_littletons wrote:

Disregard this question, a trigger could complete this request, I need a way to be notify when a new job has been created on my SQL Server. Any ideas, I was thinking to write something to monitor inserts on sysjobs table.

Years ago I created triggers for this table and they worked fine for several years helping to be notified (email) when anyone of my colleagues disable/enable jobs, create new ones and delete existing.

Have to admit, it's not recommended way, but it helped in my circumstances.

heb1014 Hall of Fame Points: 3790 More actions · Answer 5

Reports and dashboards are great. I use them regularly for monitoring. The downsides for me have been time delay and oversights. An alert could be set to near real-time. Also, an alert would not require me catching a warning on a report or dashboard.