June 4, 2010 at 3:50 am
Hi folks,
First post here, but I can't find much of a solution anywhere else so I hope you knowledgeable ones can help me out.
I have maintenance plans set up across multiple SQL Server 2008 64-bit SP1 (2531) servers which will occasionally fail. The strange thing is that the plan completes the tasks fine, but still returns a failure.
The only scheduled jobs I've seen this on are simple ones that take either a full, diff or tlog backup, then cleanup files of the same type; all created using maintenance plans. There's no mention of the failure in the SQL Server logs, and the job history only has the cryptic message below. Both the task to crate the backup, and the deletion of the old file are carried out without fault.
Description: The Execute method on the task returned error code 0x80131500 (Failed to retrieve data for this request.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Warning: 2010-06-04 02:00:17.05 Code: 0x80019002 Source: EP2MaintenancePlan Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 02:00:02 Finished: 02:00:17 Elapsed: 14.125 seconds. The package execution failed. The step failed.
Can anyone shed some light on this? I've seen problems related to early versions of 2005 SP2 similar to this, but these plans were created on 2008 SP1.
Thanks in advance!
June 4, 2010 at 4:18 am
Hi,
I've come across this error type before but only in Visual Studio while manually executing SSIS packages, which kind of makes sense as SQL Server "under the covers" will create SSIS packages when you setup a Maintenance Plan.
Possible things to try are ....
# When you manually backup the databases do you get the same issue?
# When you manually execute the Maintenance Plan do you get the same issue?
# Do all the databases that the Maintenance Plan is executing against actually exist?
# Where are the completed backup files being placed - check the disk(s) for capacity issues, IO failures or device issues.
# Script off the scheduled job associated with the Maintenance Plan, drop the original and recreate it again from script.
# Recreate the Maintenance Plan adding one database after each execution, this could help pin point the problem step/database.
Hope that helps, let me know how you get on.
Chris
www.sqlAssociates.co.uk
June 4, 2010 at 4:25 am
Thanks Chris, I'll take a look at some of those but because of the intermittent nature of the problem it's hard to test! 😀
The backups are written to a SAN disk, so it could be down to something happening there.
June 4, 2010 at 4:32 am
Your welcome.
If the storage side of things is out of your hands, that would be my starting point. Have a look at the failure history for the Maintenance Plan and send the storage guys the exact date/time the failures occured and get them to have a look at the logs. If you're using anything like an EMC Clariion CX 700, there is a wealth of information to extract.
Good luck, keep me posted!
Chris
www.sqlAssociates.co.uk
August 27, 2010 at 8:30 am
Did you ever get a resolution? I have the same situation with intermittent failures on two servers. We just built two additional servers and I'm sure this problem will multiply.
The maintenance step succeeds, while the reporting step at the end fails. It's usually a log backup since they run frequently, but has been reorgs, clean-ups, whatever.
Windows 2008 Enterprise 64-bit SP2, SQL 2008 Standard 64-bit SP1, VM-based, NetApp files. The NetApp guy sees nothing on his end. Maint plans were created fresh on SQL2008 SP1. This was not an upgrade or a VM P2V.
There is no problem once the step is removed from the maintenance plan and done in T-SQL. However, all my backup people are not DBAs, so they have asked to stay with maint plans rather than using T-SQL.
Beth Richards
Sybase, Oracle and MSSQL DBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply