DTSER_FAILURE in Maintenace Plan

• kyle.gerbrandt

  SSC Enthusiast

  Points: 104

  More actions

  March 22, 2011 at 4:20 pm

  #248535

  Hi,

  I'm posting this here because my memory is horrible and I'll need to find this solution again eventually 😀 It may also be of benefit to someone else.

  I've been struggling all week to figure out why our maintenance plans are failing with the error:

  Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2011-03-22 02:14:17.67 Source: Back Up Database Task Executing query "BACKUP DATABASE [dbname] TO DISK = N'R:\DBBa...".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:00:00 PM Finished: 2:14:18 AM Elapsed: 11656.8 seconds. The package execution failed. The step failed.

  Short Story: edit the history cleanup task if you add it.

  Long Story:

  I found the backups get written to disk but the previous backup files were not getting deleted. The maint plan history shows it ran successfully, but the job history shows it errored out. Since these backup files are 180 GBs they fill up the drive pretty quick so I've had to go in every day and delete them manually. :angry:

  I had considered using xp_delete_file but that doesn't fix the issue.

  All my maint plans include a backup task, a history cleanup task, a previous backup file cleanup task and a previous backup reports cleanup task, in that order. I noticed that our incremental backup plan didn't have a history cleanup task and it deletes the previous files and reports success in both the plan and job history.

  So I started playing with that and found if I moved the history cleanup to the end it would write the new backup, delete the old backups and reports but then error out with the error as above. Plan history says it completed successfully, job history says it didn't.

  Eventually I found that I was not editing the history task when I was adding it. Simply opening that up and closing it again fixes the problem. This also worked if the plan was already created ... just open and close the history cleanup task and save the plan.

  So if you add a history cleanup task to the maint plan, make sure to open it.