SQL keeps sending mail even after deleting maintenance plans and jobs

Question

Hai there,

weird problem: one of my SQL 2005 servers keeps sending failure mails regarding a failed job. Even after removing all maintenance plans and jobs, the servers still sends mail (every hour, according to former schedule for transaction log backup). After removing the mail profiles, the spam stops. After re-configuring the mail profile SQL automatically starts sending the alert mails again.

Anyone any idea?

Is there still an entry in a system table?

Thanks in advance for the help!

Answer 1

Just to confirm one more time. All the related jobs have been removed apart from the Maintenance Plans.

 

---

Pradeep Adiga
My blog: http://www.sqldbadiaries.com

Answer 2

I removed all maintenance plans and SQL automatically removed all jobs as it should.

I just traced that it's not a notification problem: SQL really tries to backup the transaction logs.

I will move this thread to another forum, since it's not a notification problem anymore.

It's still a very weird problem: the msdb maintenance plan en job tables are all empty, logshipping is not configured and the logshipping related msdb tables are empty. It could be an old entry left behind by mirroring, but I don't know in which tables to look for that.

Thanks for your reply!

Answer 3

Hi,

According to your description, you mean that after deleting the maintenance plans and jobs, the SQL Server still keeps sending email, right? Please refer to the following suggestions:

1.    Check whether the job is enabled to be executed. Please see:

          SELECT name as job_name,date_created FROM msdb..sysjobs WHERE [enabled]=1

2.    Execute the following query to get the date and time that the mail was sent:

          SELECT sent_date FROM msdb..sysmail_allitems WHERE sent_status='sent'

           Note: we can also get other information processed by Database Mail, please see:

           sysmail_allitems (Transact-SQL)

3.     Check out which job was running when the Email was sent. Please see the following query which is used to get some information about job:

SELECT job_name, run_datetime, run_duration

FROM

(

    SELECT job_name, run_datetime,

        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duratiON, 3, 2) + ':' +

        SUBSTRING(run_duration, 5, 2) AS run_duratiON

    FROM

    (

        SELECT DISTINCT

            j.name AS job_name,

            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) + 

                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,

            run_duration = RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6)

        FROM msdb..sysjobhistory h

        inner join msdb..sysjobs j

        ON h.job_id = j.job_id

    ) t

) t

           ORDER BY job_name, run_datetime

 

For more information, please see： 

sysjobhistory (Transact-SQL)

sysjobs (Transact-SQL)

If you have any more questions, please feel free to let me know.

 

Thanks,

Ai-Hua Qiu

---

Constant dropping wears away a stone.

Answer 4

Mr. Adiga,

Did you find a solution to this problem?  I am experiencing the exact same issue with my SQL Server 2005 instance.

Any help would be greatly appreciated.

-

Michael Leslie