June 27, 2014 at 1:12 pm
I have a maintenance plan in Sql Server 2008 R2 where the durations shown do not add up to the total time spent. The shown durations show 55 minutes but the entire plan takes 8 hours 37 minutes!
It consists of 6 steps in a Maintenance Plan (SSIS): (Name, duration, shown start time)
Check Database Integrity, 20 min, 9:45pm
Reorganize Indexes, 17 min, 2:35am
Update Statistics, 11 min, 6:04am
Full Database Backups, 7 min, 6:16am
Cleanup old Backups, 0 min, 6:23am
Cleanup old maintenance reports, 0 min, 6:23am
(I realize reorganize index here is probably a waste but haven't had time to update this inherited maintenance plan.)
Both the weekly and the daily jobs are experiencing this issue.
What I have done so far:
The first version of this maintenance included a shrink database (like I said, I inherited this) which I first disabled and then removed. Reading up on SSIS and maintenance plans it seems like you shouldn't change them ever and expect them to work so I deleted that plan and created a new one from scratch (different job and task names, just in case) This didn't change the overall duration or time gaps.
I verified that that Database Integrity checks are occurring in the 20 minute window as shown.
The backups did happen at 6:16am+.
We have restarted SQL Server Agent, no affect.
Any ideas on what is going on here and how I can resolve this?
July 3, 2014 at 8:41 am
Quick update for anyone experiencing this in the future.
This was due to a bug in Sql Server where the durations are completely wrong when viewed from the view history on the Maintenance Plan. (View history from the Agent job doesn't show detail so this is where the confusion came in.) I had tasks that were unexpectedly taking hours to complete and the $&!@#$ logs were not showing it.
In my Google searches I did find this issue but it was for 2005. I am astounded this bug is still present. I imagine Microsoft would tell me to upgrade to 2012 for a fix. Wow, just .... wow.
I will be eliminating any use of Maintenance Plans in my systems as they are just too buggy and don't get the job done.
If you have to use the maintenance plans keep them to one action per plan and remember to verify it is doing what you think it is doing. Removing old database backups is wonky in particular.
Good luck!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply