SQL Server Performance Forum – Threads Archive
Maint Plan and Backup failure Question
Hi everyone,I have a maint plan that take backup of all DBs in our system. when a backup of a DB fails alerts dont work sometime. I am used to creating jobs that perform backup and send alerts to me if there is any failure (I am doing fine with this). However, The manager wants me to create a job or script that send alerts to us when a backup of a DB in the maint Plan fails. any help will be appreciated. thanks
Shabnyc
Configure the db mail and use send send mail task to send the notification in case of failure of any backup in maintenace plan (SSIS)… I don’t think database backup failure information written to any system tables other than sql error.
You may need to parse the sql error using sp-readerrorlog procedure and send the email notification using sp_send_dbmail procedure if you have db mail configured…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
I do have db mail configured. can this be done using another Job othere than the plan job?
I also forced the maint plan to fail backing up one DB but found nothing in the error log related to the failure.
You can make use of the following script… SELECT mp.[name],
msp.subplan_name,
mpl.start_time,
mpl.end_time,
mpld.command,
mpld.error_message
FROM msdb.dbo.sysmaintplan_plans mp
JOIN msdb.dbo.sysmaintplan_subplans msp ON mp.id=msp.plan_id
JOIN msdb.dbo.sysmaintplan_log mpl ON msp.subplan_id=mpl.subplan_id
JOIN msdb.dbo.sysmaintplan_logdetail mpld ON mpl.task_detail_id=mpld.task_detail_id
WHERE mp.[name]=N’Pubs_NW_tl_backup’ AND mpl.succeeded = 0
ORDER BY mpl.start_time DESC;
http://www.sql-server-performance.com/ss_creating_backup_jobs.asp
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
I have seen this issue lot of times when you have MP configured with DB mail for alerting, I would ask whether you have updated service pack on SQL Server instance. http://sqlserver-qa.net/blogs/tools…es-with-maintenance-plans-scheduled-jobs.aspx fyi.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Thanks Muhamed, Thanks Satya for your help. here is what might make me confued:
in the backup MP, I changed one DB’s name in order to make the Job fail. The MP fails with error, but the (Job) for this MP shows successful execution. I am not sure how I am going to be notified when this occurs. I have 2005 SP2
Might be undelying SSIS package is not changed or the version of ssis package might be different… Try creating the new package with correct db name with correct path (c:ackup)…
once the MP is created rename the backup folder (c:ackup1) so that your MP fails AND see you get the notification or not…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Can you start another fresh MP for a backup in this along with new SSIS package.
quote:Originally posted by shabnyc
Thanks Muhamed, Thanks Satya for your help. here is what might make me confued:
in the backup MP, I changed one DB’s name in order to make the Job fail. The MP fails with error, but the (Job) for this MP shows successful execution. I am not sure how I am going to be notified when this occurs. I have 2005 SP2
in the backup MP, I changed one DB’s name in order to make the Job fail. The MP fails with error, but the (Job) for this MP shows successful execution. I am not sure how I am going to be notified when this occurs. I have 2005 SP2
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>