Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
juancabrera
Starting Member
30 Posts |
 Posted - 2009-08-20 : 16:21:25
|
| I have been asked by my manager to notify sql operators when:- Job Starts, Ends, Outcome of Job.- Job Step Starts, Ends, Outcome of Job Step.1. Is there a way to use SQL Alerts for this? Or are SQL alerts only limited to DDL events?2. What is the recommended aproach? |
|
|
juancabrera
Starting Member
30 Posts |
Posted - 2009-08-20 : 19:25:19
|
| The only way I can seem to do it, which I don't necessarily like is by altering the following stored procedures:- [sp_start_job]- [sp_stop_job] OR- [sp_sqlagent_log_jobhistory] - this sp is only triggered after the step has finished.So what I am going to do is alter the start and stop stored procedures in msdb to insert records on my own custom table. This new custom table will be monitored by a job every n seconds/minutes which will email/page a sql operator and notify that the job started/stopped/completed/succeded/failed.Anyone has any better ideas? Remember, I don't care to know when the job started, it is just something I am being required to do.thanks.Juan. |
 |
|
|
forjo
Starting Member
1 Post |
Posted - 2009-08-28 : 07:30:37
|
| Altering the SQL Server provided stored procedures is an interesting idea. However it's dangerous because they may be replaced when updates are applied.I have another idea:Via the Job Properties, Notifications Tab you can email/page when the job completes.If that doesn't meet your requirements, the next idea:Use sp_help_jobhistory / msdb.dbo.sysjobhistory / msdb.dbo.sysjobs / msdb.dbo.sysjobsteps to get the desired informations.Let me know what you think about it. |
|
|
|
|
|
|
Job Monitoring