Looking through the Database Mail log today, I accidentally discovered a job that had been busy sending emails for I-don’t-know-how-long using an email profile that no longer worked. The output of the job was ‘success’ as the emails had been successfully queued with the Database Mail sub-system.
After finding the emails would have been empty anyway, I disabled the job. But it made me wonder if there might be other jobs that were busy doing nothing – hour after hour – day after day.
Knowing the dangers of weakening the system, I did not want to fail a job or job-step just to flag a maintenance issue.
The lowest-risk change I could think of making (to the many, legacy, unfamiliar jobs) was to leave pertinent messages in the job history log using the PRINT command. For example:-
IF EXISTS (SELECT 1 FROM SomeTable)
BEGIN
PRINT 'YES: there is new data'
(Do meaningful stuff)
END
ELSE
PRINT 'NO: there is no new data';
Then in the future I might notice that there is Never any new data!
Database Consultant 