SQL Server sending backup job failure notification - After deleting also

Question

SQL Server sending backup job failure notification - After deleting also

ramana3327

SSCoach

Points: 19700
More actions
January 17, 2017 at 6:01 am

#325476

Hi,
We created the maintenanace plan for backup job and scheduled the job. We deleted the maintenanace plan. Even though the maintenance plan and job deleted we receiving the alert about job failure. How can we avoid this?
We are alerady have the SP3 applied (10.50.6000).
I can't able to find my posts tab in SQLSERVERCENTRAL.COM. How can we view my posts?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

ramana3327 - Tuesday, January 17, 2017 6:01 AM
Hi,
We created the maintenanace plan for backup job and scheduled the job. We deleted the maintenanace plan. Even though the maintenance plan and job deleted we receiving the alert about job failure. How can we avoid this?
We are alerady have the SP3 applied (10.50.6000).
I can't able to find my posts tab in SQLSERVERCENTRAL.COM. How can we view my posts?

Don't use maintenance plans would be one of the best ways in my opinion. They just seem to have so many issues that I can't see how they save time for anyone. Did you check the send mail logs? You should be able to find the mail that was sent with that:
select * from msdb.dbo.sysmail_allitems

You should be able to take a line out of the emails you get to search on the email as well. The body column of sysmail_allitems has the text of the email that is sent. I'd check that to make sure it's coming from the server you think it is coming from.
You could also query sysjobs where notify_level_email > 1 to check the jobs that are sending out notifications.
You should have some clues by querying those. It's likely the job is still there or the alert is coming from another server, environment.
Alerts aren't sent out from jobs that have been deleted so you may want to proceed as if the job does exist somewhere and find where the alert is coming from.

Sue

ramana3327 SSCoach Points: 19700 More actions · Answer 2

Thanks for the reply. Let me give you some more details.

We have the maintenanace plan created around 3 yr ago with SQL 2005. We migrated to 2008 R2 almost 1 yr ago. Everything is working fine till now.

In that backup which is running that maintenance plan has additional step which will send us custom notification about the job failure. Almost from last 10 days, we noticied the for the same scheduled the maintenance plan is running twice and we are getting 2 backups. So we disabled that job and created new maintenance plan for the backup but still it is running twice.

So we deleted both of the backup maintenance plans. After deleting that maintenance plans & backups jobs, we are receiving the job failure alert, which is added as additional step in old job.

I am sure, the alert is coming from the same instance because in the alert we will receive the instance name and the job name where it failed.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

ramana3327 - Wednesday, January 18, 2017 8:24 PM
Thanks for the reply. Let me give you some more details.
We have the maintenanace plan created around 3 yr ago with SQL 2005. We migrated to 2008 R2 almost 1 yr ago. Everything is working fine till now.
In that backup which is running that maintenance plan has additional step which will send us custom notification about the job failure. Almost from last 10 days, we noticied the for the same scheduled the maintenance plan is running twice and we are getting 2 backups. So we disabled that job and created new maintenance plan for the backup but still it is running twice.
So we deleted both of the backup maintenance plans. After deleting that maintenance plans & backups jobs, we are receiving the job failure alert, which is added as additional step in old job.
I am sure, the alert is coming from the same instance because in the alert we will receive the instance name and the job name where it failed.

Something isn't being done correctly if the maintenance plan job starts running twice. Disable it and it still runs. Delete maintenance plan and jobs and still sends alerts. You really do need to start going through the tables to find the issue as something is getting missed in the process of disabling, deleting or however these are being managed.
I would probably start querying msdb.dbo.sysjobhistory, msdb.dbo.sysmaintplan_logdetail. These both have dates that you can reference and relate based on the times of the notifications.
You can also go through msdb.dbo.sysmaintplan_subplans to make sure the number of jobs, maintenance plans are the same as what you see in Object Explorer.
I would probably still check the log tables for database mail since many of the values in the mail that is sent can be hard coded so it may not be what it seems to be.

Sue

ramana3327 SSCoach Points: 19700 More actions · Answer 4

Sue_H - Thursday, January 19, 2017 11:40 AM
ramana3327 - Wednesday, January 18, 2017 8:24 PM
Thanks for the reply. Let me give you some more details.
We have the maintenanace plan created around 3 yr ago with SQL 2005. We migrated to 2008 R2 almost 1 yr ago. Everything is working fine till now.
In that backup which is running that maintenance plan has additional step which will send us custom notification about the job failure. Almost from last 10 days, we noticied the for the same scheduled the maintenance plan is running twice and we are getting 2 backups. So we disabled that job and created new maintenance plan for the backup but still it is running twice.
So we deleted both of the backup maintenance plans. After deleting that maintenance plans & backups jobs, we are receiving the job failure alert, which is added as additional step in old job.
I am sure, the alert is coming from the same instance because in the alert we will receive the instance name and the job name where it failed.
Something isn't being done correctly if the maintenance plan job starts running twice. Disable it and it still runs. Delete maintenance plan and jobs and still sends alerts. You really do need to start going through the tables to find the issue as something is getting missed in the process of disabling, deleting or however these are being managed.
I would probably start querying msdb.dbo.sysjobhistory, msdb.dbo.sysmaintplan_logdetail. These both have dates that you can reference and relate based on the times of the notifications.
You can also go through msdb.dbo.sysmaintplan_subplans to make sure the number of jobs, maintenance plans are the same as what you see in Object Explorer.
I would probably still check the log tables for database mail since many of the values in the mail that is sent can be hard coded so it may not be what it seems to be.
Sue

I query the sysjobs tables and get the jobids. It matches the no of jobs in objectexplorer. I query the job history table with filtering the jobis which doesn't exits in sysjob tables jobid. I got zero records. i.e. The other jobs are completely deleted with deletion of the job the related job history got deleted.
I query the sysmaintplan_subplans. It matches to the no of maitnenanceplan that exists currently on the server.
sysmaintplan_log has records only existing subplan_ids. It is confirming that maintenanceplans are deleted properly.

ramana3327 SSCoach Points: 19700 More actions · Answer 5

Thanks for the help.

We figured it out that the alert is coming from different server. They build the new server using the clone of the production server.
These are coming from the new server.