May 10, 2011 at 9:09 am
I have a 2005 SP3 Ent edition instance with a maintenance plan subtask executing the Maintenance Cleanup Task. It runs every 15 mins and deletes *.TRN files over 2 days old.
The job has run fine for 2 years. But it has failed the last two days at 6:35am with:
Executed as user: xxxxx\xxxxx ...035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 6:35:00 AM Progress: 2011-05-10 06:35:01.13 Source: {CD67F40B-436D-4EA8-AB6E-B6606969CBFB} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Error: 2011-05-10 06:35:01.25 Code: 0xC0024104 Source: Reporting Task for subplan-{F0C6926E-130A-4547-B441-5A445A61F5EF} Description: The Execute method on the task returned error code 0x80131931 (Data is Null. This method or property cannot be called on Null values.). The Execute method must succeed, and indicate the result using an "out" parameter.
My first thought was something else (netbackup?) is locking the file that the job wants to delete but the error message does not sound like that. Anyone have other thoughts? The usual Googling is not finding much... thanks
May 10, 2011 at 9:15 am
Is it possible that the file has been manually deleted, moved, locked, different permissions or something?
Hard to explain 2 years of working then failing only once daily.
Anything else accessing that file for backups?
May 10, 2011 at 2:20 pm
That looks like the job history - check the maintenance plan history instead. It should show you why that specific task failed.
Now, not sure why you would perform rolling deletes every 15 minutes for your log backups. Seems a bit of overkill to me.
What I do is identify the full backup that I want to keep (current, previous, prior, etc...) and delete all files older than that file - and run that right after a successful full backup.
This way, I can identify the number of full backups I want to keep and delete everything before that. The code I use can be run multiple times and will not delete any files that should not be deleted.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 11, 2011 at 5:22 am
Jeffrey Williams-493691 (5/10/2011)
That looks like the job history - check the maintenance plan history instead. It should show you why that specific task failed.Now, not sure why you would perform rolling deletes every 15 minutes for your log backups. Seems a bit of overkill to me.
What I do is identify the full backup that I want to keep (current, previous, prior, etc...) and delete all files older than that file - and run that right after a successful full backup.
This way, I can identify the number of full backups I want to keep and delete everything before that. The code I use can be run multiple times and will not delete any files that should not be deleted.
Exactly what I do here. Get the list of files, figure out the ones I don't need and flush the rest. Runs once daily in 1 sec and I never have "unneeded" backupfiles untill 1 sec before I run this. Which is right after I've finished the test restore of the daily backup and that checkdb returned nothing.
May 11, 2011 at 6:49 am
Thanks for the ideas. BTW, the job failed again today 6:35am.
Yes, that message was from the job history. Another part of this mystery is that the job reports the failure but the maintenance plan history shows success for the 0635 run.
I agree, running the cleanup every 15 mins is not necessary. I will create a job or step that deletes the old backups after the nightly full backup.
But this failure is a mystery. My only guess is that Netbackup or something else is locking files when this job tries to delete them. I don't have visibility into the Netbackup start/end times but I can request that info. But I know it runs longer than 15 mins so it's strange this has started failing daily at 0635 while the 0620 and 0650 runs succeed.
May 11, 2011 at 6:52 am
Ken Davis (5/11/2011)
Thanks for the ideas. BTW, the job failed again today 6:35am.Yes, that message was from the job history. Another part of this mystery is that the job reports the failure but the maintenance plan history shows success for the 0635 run.
I agree, running the cleanup every 15 mins is not necessary. I will create a job or step that deletes the old backups after the nightly full backup.
But this failure is a mystery. My only guess is that Netbackup or something else is locking files when this job tries to delete them. I don't have visibility into the Netbackup start/end times but I can request that info. But I know it runs longer than 15 mins so it's strange this has started failing daily at 0635 while the 0620 and 0650 runs succeed.
My first suspect would then be the job report query itself. You've just proven it to be wrong.
May 11, 2011 at 7:09 am
By "job report query" do you mean the process that logs the maintenance plan success/fail or something else? Can you share any hints for troubleshooting the job report query?
I will plan to run a trace when the job has been failing and see what I catch.
May 11, 2011 at 2:26 pm
I will say it again - look at the maintenance plan history and not the job history for better information about the error.
Right-click on the maintenance plan and View History.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 11, 2011 at 2:31 pm
Yes, I understand how to do that. The job history shows the failure and the maintenance plan history shows success. I know that's not supposed to happen but it's true.
May 11, 2011 at 3:16 pm
Yes - sorry, missed that part. Looking further at the message, do you have the maintenance plan setup to create a report?
This is configured using the little button next to Manage Connections. If you have that enabled, it is trying to create a report and is not able to access the report file or directory.
Since the reports that are generated from this are really quite useless - I turn it off. And, if you are generating those reports - did you include a task to clean them up?
What you can do is add an output file to the job - that should allow you to capture the full message when it errors and help you identify the exact problem.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 12, 2011 at 5:32 am
Ken Davis (5/11/2011)
Yes, I understand how to do that. The job history shows the failure and the maintenance plan history shows success. I know that's not supposed to happen but it's true.
I've seen a similar situation where on both success and failure the job was set to report success... maybe that's the issue here.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply