SQL Server – Cannot Find Job Associated with Active Email Notifications

sql serversql-server-2012ssmst-sql

We have a email notification going via our SQL Server however I cannot find any job or setting associated with it. Its definitely the correct server and I am logged in as admin. The email is sent via the email operator as setup on the associated server. The email is definitely sent from the SQL server in question as when I query the msdb in the sysemail_mailitems the email the recipients received definitely exists as shown in the screenshot below. All we want to do is change the recipients for the notification but only if we knew where….

enter image description here

Best Answer

It is probably either coming from a job or within a stored procedure somewhere. If it's a stored procedure, then after you find it, use the job search from below to look for the job calling that sp.


---- Search Job Steps
SELECT 
J.name AS JobName,
JS.step_name AS StepName,
JS.step_id AS StepNumber,
JS.command

FROM 
msdb.dbo.sysjobs J
INNER JOIN msdb.dbo.sysjobsteps JS
ON J.job_id = JS.job_id

WHERE
JS.command LIKE '%XXXXXX%'


---- Search Stored Procs, might want to loop through all databases 

USE XXXXX


SELECT 
DB_NAME() AS DatabaseName,
SCHEMA_NAME(O.schema_id) AS SchemaName
O.name AS SPName,
MO.definition AS SPDefinition

FROM
sys.sql_modules MO
INNER JOIN sys.objects O
ON MO.object_id = O.object_id

WHERE
MO.definition LIKE '%XXXXXX%'

Replace the WHERE clause with a short bit from the email itself that you have confidence would be in the body of the definition. It's not obvious from your post if some of that content in the email is dynamic or the same every time.

Related Question