SP sending email alerts

Question

SP sending email alerts

danny3291

SSC-Addicted

Points: 436
More actions
August 8, 2018 at 10:09 am

#357498

Good afternoon,
Forgive me but I am new to this, I have been given a SQL server to look at that has an SP that sends an email out if disk space gets too low. My issue is I am trying to find out what is triggering it, it has an agent job set up but the schedule is set to disabled so how is it being fired every hour?
I cannot see any triggers or any other program that could be executing it. Any ideas?

Viewing 15 posts - 1 through 15 (of 15 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

danny3291 - Wednesday, August 8, 2018 10:09 AM
Good afternoon,
Forgive me but I am new to this, I have been given a SQL server to look at that has an SP that sends an email out if disk space gets too low. My issue is I am trying to find out what is triggering it, it has an agent job set up but the schedule is set to disabled so how is it being fired every hour?
I cannot see any triggers or any other program that could be executing it. Any ideas?

Did you check for any alerts related to the notification? Those can also execute jobs in response.

Sue

andycadley SSCertifiable Points: 5296 More actions · Answer 2

Check for something calling sp_start_job to run the job. A job can be executed that way without a schedule or even if the job itself is set to disabled.

danny3291 SSC-Addicted Points: 436 More actions · Answer 3

Thank you both for the help, I cannot see any notifications set up and I have checked the code and I cannot see an sp_start_job. any other ideas?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

SELECT a.name AS AlertName , j.name AS JobName , s.step_id , s.command FROM msdb.dbo.sysalerts a JOIN msdb.dbo.sysjobs j ON a.job_id = j.job_id JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id WHERE a.job_id <> '00000000-0000-0000-0000-000000000000'

John

danny3291 SSC-Addicted Points: 436 More actions · Answer 5

Thank John, that returns no results, do you think It could be a PowerShell set up on the server, I don't have direct access so I cannot check at the moment.

danny3291 SSC-Addicted Points: 436 More actions · Answer 6

Found that its the SQL agent, when I go into manage schedules and then find the correct one, then steps and then jobs in schedule and it shows as enabled.

How come this doesn't show enabled if I go direct into SQL agent and find that job?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

This will tell you who is running the job - that may give you a clue to how the job is being run. (Don't forget to substitute in the name of your actual job.) If it doesn't, I think you'll need to run an Extended Events session to capture executions of sp_start_job.

SELECT h.run_date , h.run_time , h.message FROM msdb.dbo.sysjobhistory h JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id WHERE h.step_id > 0 AND j.name = 'JobWithNoSchedule';

John

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

danny3291 - Thursday, August 9, 2018 3:20 AM
Found that its the SQL agent, when I go into manage schedules and then find the correct one, then steps and then jobs in schedule and it shows as enabled.
How come this doesn't show enabled if I go direct into SQL agent and find that job?

What do you get if you run this?
SELECT j.name AS JobName , j.enabled AS IsJobEnabled , s.name AS ScheduleName , s.enabled AS IsScheduleEnabled FROM msdb.dbo.sysschedules s JOIN msdb.dbo.sysjobschedules js ON s.schedule_id = js.schedule_id JOIN msdb.dbo.sysjobs j ON js.job_id = j.job_id WHERE j.name = 'JobWithNoSchedule';

John

danny3291 SSC-Addicted Points: 436 More actions · Answer 9

So I have put in the name of the job and ran the script and it shows the job as enabled but the is schedule enabled column there is a 0. but when I check the history the job is running every hour.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

Is the job running to the schedule that appears to be disabled (with the same frequency and at the same times)? Have you run the sysjobhistory query I posted earlier to check who's running the job? Could it be a Windows scheduled task that's doing it?

John

danny3291 SSC-Addicted Points: 436 More actions · Answer 11

Just says Executed as user: NT SERVICE\SQLAgent. The step succeeded.

danny3291 SSC-Addicted Points: 436 More actions · Answer 12

and yes it is running as per the created schedule its just showing as disabled.

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 13

danny3291 - Wednesday, August 8, 2018 10:09 AM
Good afternoon,
Forgive me but I am new to this, I have been given a SQL server to look at that has an SP that sends an email out if disk space gets too low. My issue is I am trying to find out what is triggering it, it has an agent job set up but the schedule is set to disabled so how is it being fired every hour?
I cannot see any triggers or any other program that could be executing it. Any ideas?

Could be from a Windows Scheduled Task. This would make sense as it's probably easier to write a powershell or DOS batch file to check for space and then get it to call a SQL Server stored procedure if it's out of space to send an email.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 14

I really don't know what's happening, then. If you're more interested in fixing this than finding out why it's happening, check that the schedule isn't being used for any other jobs, then drop the schedule. If you still need it to be there so that it can be re-enabled at the flick of a switch, set the schedule up in the same way again, and disabled it.

John