Failed Maintenance Plan with DTSER_FAILURE error in SQL Server

If you get an error error in SQL Server Job History log when executing a Maintenance Plan (for example "Check Database Integrity Task") that looks similar to this:

"Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:18:32 Finished: 9:18:34 Elapsed: 2.075 seconds. The package execution failed. The step failed."

...and the same task runs manually without an error, make sure that 'allow updates' option is set to 0.

EXEC sp_configure;
GO

If the run_value of the "allow updates" option is 1, execute the following code:

EXEC sp_configure 'allow updates', 0
GO
RECONFIGURE
GO

Maintenance plan execution can fail for different reasons, for example Update Statistics Maintenance Plan that started running while Rebuild Index Maintenance Plan has not finished can fail:

Message
Executed as user: NT Service\SQLSERVERAGENT... Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Error: Code: 0xC0024104 Source: Update Statistics Description: The Execute method on the task returned error code 0x80131904 (Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error DTExec: The package execution returned DTSER_FAILURE (1). The package execution failed. The step failed.

or:

Message
Executed as user: NT Service\SQLSERVERAGENT... Source: {0584E6E1F-5487-1A48-234B-F22885478968} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Error: Code: 0xC0024104 Source: Update Statistics Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error DTExec: The package execution returned DTSER_FAILURE (1). The package execution failed. The step failed.

Check the schedules for jobs, and duration from job history. Duration of Index and Statistics maintenance jobs can increase with time as the database grows. Make sure that the jobs don't run at the same time.

If the post helped you, please share it:
Pin It

1 comment to Failed Maintenance Plan with DTSER_FAILURE error in SQL Server

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">