troubleshooting failed "history cleanup task" - where to look?

Question

troubleshooting failed "history cleanup task" - where to look?

josebrwn

Old Hand

Points: 393
More actions
April 19, 2010 at 10:42 am

#386734

I'm trying to figure out a failed "history cleanup task" - this is just the standard maintenance plan task. The Server Agent user has sysadmin, and when I run sp_purge_jobhistory manually it works fine.
The information I get back from the job history is incomplete, and the text file in the logs folder tells me nothing. As you can see from the job history, it's not really telling me what I need to know. Is there any way to get more useful information? Thanks!!
Message
Executed as user: [....]. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:31:48 PM Progress: 2010-04-19 12:31:52.28 Source: {C1BBE7F3-E70F-4ED6-A9AE-24504FC7698F} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:31:48 PM Finished: 12:31:53 PM Elapsed: 4.477 seconds. The package execution failed. The step failed.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

josebrwn (4/19/2010)
I'm trying to figure out a failed "history cleanup task" - this is just the standard maintenance plan task. The Server Agent user has sysadmin, and when I run sp_purge_jobhistory manually it works fine.
The information I get back from the job history is incomplete, and the text file in the logs folder tells me nothing. As you can see from the job history, it's not really telling me what I need to know. Is there any way to get more useful information? Thanks!!
Message
Executed as user: [....]. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:31:48 PM Progress: 2010-04-19 12:31:52.28 Source: {C1BBE7F3-E70F-4ED6-A9AE-24504FC7698F} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:31:48 PM Finished: 12:31:53 PM Elapsed: 4.477 seconds. The package execution failed. The step failed.

Where u checked ?

I think u r checked job history.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

josebrwn Old Hand Points: 393 More actions · Answer 2

Yes, the quote is from job history. It never tells you very much! Is there a query that gives more complete information?

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 3

josebrwn (4/19/2010)
Yes, the quote is from job history. It never tells you very much! Is there a query that gives more complete information?

select [error_message] from msdb.dbo.sysmaintplan_logdetail

where succeeded =0 order by [error_message] desc

OR

---> Right click maintenance Plan --> View history

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

josebrwn Old Hand Points: 393 More actions · Answer 4

right... so I found a blog that talked about a similar problem and referred to the "useless, truncated message" you get when these plans fail. I liked that 😉

so I just looked at the profiler. every thing's okay until this:

select * from msdb..sysmaintplan_logdetail where task_detail_id = '9404B7CB-A548-4226-A24D-49A79A1AAC57' order by start_time,end_time

the query returns no rows, and then the error is thrown.

is there something about the "history cleanup task" (that ships with the maintenance plan tasks) that doesn't work in 2008?

C.J. Morgan SSC Enthusiast Points: 198 More actions · Answer 5

Did you ever find a fix for this? I've experienced the same thing, I can run the whole package which also cleans up old .bak & .trn files fine. I've removed the History Cleanup Task and now it runs as scheduled fine too.

Thanks in advance.

CJ

taseedorf SSC Eights! Points: 917 More actions · Answer 6

Old topic, I know, but my guess is that that job is failing because the backup history is too large....my guess is you've got many many rows in that table.. you'll need to work some mahic!