Historic monitoring referencing obsolete code

What this check looks for

Agent job steps whose command text contains senddatabasestatusemail, found by joining msdb.dbo.sysjobs to msdb.dbo.sysjobsteps. The message names the job.

The check is skipped on Amazon RDS.

Why it matters

SendDatabaseStatusEmail was part of the Stedman Solutions 24×7 monitoring setup and has been deprecated and replaced. A job that still calls it is in one of two states, and both are worth clearing up:

• The procedure is gone, so the job fails every time it runs. That is noise in the job failure list, and noise in the failure list is how a real failure gets missed. It also means whatever notification that job was sending has silently stopped.
• The procedure is still there but is no longer maintained. This is the worse case, because the job succeeds. Nobody investigates a job that succeeds, and the status email nobody is receiving looks exactly like the status email nobody needed.

Either way, something that was set up to tell you about your databases is not telling you anything, and the absence of a report reads the same as a clean report.

How to confirm it yourself

Find the job and the exact step:

SELECT j.[name]        AS [job_name],
       j.[enabled],
       s.[step_id],
       s.[step_name],
       s.[subsystem],
       s.[command]
  FROM msdb.dbo.sysjobs AS j WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysjobsteps AS s WITH (NOLOCK)
         ON s.[job_id] = j.[job_id]
 WHERE LOWER(s.[command]) LIKE '%senddatabasestatusemail%'
 ORDER BY j.[name], s.[step_id];

Does the procedure still exist anywhere?

SELECT DB_NAME() AS [database_name], [name], [type_desc], [create_date], [modify_date]
  FROM sys.objects WITH (NOLOCK)
 WHERE [name] LIKE '%SendDatabaseStatusEmail%';

And what has the job actually been doing?

SELECT j.[name],
       h.[run_date], h.[run_time], h.[run_duration],
       CASE h.[run_status] WHEN 0 THEN 'Failed'
                           WHEN 1 THEN 'Succeeded'
                           WHEN 2 THEN 'Retry'
                           WHEN 3 THEN 'Canceled'
                           ELSE 'Unknown' END AS [status],
       h.[message]
  FROM msdb.dbo.sysjobhistory AS h WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysjobs AS j WITH (NOLOCK)
         ON j.[job_id] = h.[job_id]
 WHERE LOWER(j.[name]) LIKE '%status%'
 ORDER BY h.[run_date] DESC, h.[run_time] DESC;

How to fix it

Decide what the job was for, then choose one of three.

1. You still want the notification. Replace the job step with the current mechanism. Database Health Monitor’s own alerting covers this ground, and so does a SQL Agent alert with an operator for the specific conditions you care about.
2. You do not want it. Delete the job. Leaving a disabled job behind is tidier than a failing one but it still appears in every inventory and every migration.
3. You are not sure. Disable it rather than deleting it, and note the date. A disabled job nobody misses for a quarter is a job that can be deleted.

-- reversible first step
EXEC msdb.dbo.sp_update_job @job_name = N'YourJobName', @enabled = 0;

Before you delete anything, check who the operator was. The job’s notification settings say who was supposed to be told, and that person may still be expecting to hear something.

How long it takes

About an hour, most of it working out what the job was originally for.

Related reports

Related checks

Frequently asked questions

We do not use the Stedman monitoring system. Then the job is a leftover from an evaluation or a previous administrator, and deleting it is straightforward.

The job succeeds. Is there really a problem? That is the case worth looking at most closely. A succeeding job that calls a procedure nobody maintains is sending something nobody reads, or nothing at all.

Can I just remove the step and keep the job? Yes, if the job does other useful things. Check the remaining steps and the workflow between them, because removing a step can change what runs next on success or failure.

How do I find other obsolete references? The Technical Debt report sweeps for this kind of thing across the instance, rather than one known procedure name.