Trace and locate a job that is sending out an email

[daglugub37]

There is a particular email that is produced each day and sent out with a huge attachment of the recordset.

How would I go about in querying for the job that is doing this.

I am almost sure I know of the stored proc with a xp sendmail commands that is doing this, so with the name of the stored proc is there a way I can find out the scheduled job that is executing it. Perhaps there is a systems table that may contain some notes field?

 

[dbomrrsm]

You could script all your procs to a text file then search - I would search for the file name that is being sent as an attachment - that way it will only find the specific SP rather than all that use xp_sendmail.

To script all yours procs quickly in EM right click the DB select all tasks - generate sql scripts. In the window that appears click show all then select the check box next to stored procedures and press preview - you can then cut and paste this to search in a text editor.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[JamesLean]

If you know the SP name then you can use this query to find out which job(s) is executing it:

USE msdb

SELECT j.name, js.step_name, js.command
FROM sysjobsteps js JOIN sysjobs j ON js.job_id = j.job_id
WHERE js.command LIKE '%my_proc_name%'

--James

 

[daglugub37]

Thanks all...both suggestions are very helpful. I still can not find it; but I think I am working off of bad info...these methods are great