SQL Server 2008 maintenance jobs randomly failing when tasking out to SQL 2005
I’m hoping someone here can shed some light on an issue I’m experiencing with SQL 2008. The short version of it is that my maintenance plans are intermittently failing. When I examine the SQL agent logs, the failing entries all contain the line:
“Description: The Execute method on the task returned error code 0x80131500 (Failed to retrieve data for this request.). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error”
Of course I’ve done the usual searches on this error message and all I’ve found is mention of problems with maintenance plans on pre-SP2 SQL2005.
Now here’s the extended version of the issue. What I’m doing actually involves 3 SQL servers all on separate hardware. The one that runs the maintenance plan is SQL 2008 SP 1 and the two others run SQL 2005 SP3. Within the 2008 hosted server maintenance plan are three subplans, each containing 1 backup task for each server. There is one subplan each for full backup, differential backup and transaction log backup. Of course, the tasks that do the backups on the other servers utilize separate connections to those servers and every time I’ve run them manually they’ve worked without problem. However when the subplan is scheduled to run as a job, the tasks randomly fail approximately 1/3 of the time. The failures,, when they occur, are distributed evenly among the servers and always write the above error message to the agent log. Strangely the maintenance plan log always indicates success, incorrectly I think, as the backups really aren’t being done (no file is created for a particular server when the agent indicates a failure on that task). One other detail is that if I remove the tasks for the external 2005 servers the failures disappear. Also if I run the backups from one of the 2005 boxes and only include the 2 2005 boxes then the failures disappear.
Other info is that these are all 64 bit SQL Standard Edition, running on 64-bit Windows Server 2008. I have deleted and recreated the maintenance plans but this didn’t solve anything.
It’s pretty important that I get this working so any help or insight would be much appreciated. Thanks in advance.
All Replies
- A maintenance plan can be edited (OE: Modify) pretty much like an SSIS package. It is very helpful if you know SSIS. Have you tried?
Can anyone assist?
Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
