Where has the SQL Server maintenance plan history gone to?

I recently went to review the history of various maintenance plans that i have set up for things like backups, runstats and reorgs, etc.

None of it was there.

There is a task in the daily clean up maintenance plans that removes old bak and trn files. It also removes history older than 4 weeks. Could this be the culprit? But supposed to keep 4 weeks worth.

I checked on the agent history and it exists. I just don't have any maintenance plan history.

What is causing this behaviour and what can I do to get the plan history to start being saved again?

This is happening in both SQL Server 2005 and SQL Server 2012

TIA!
data_bitsdbaAsked:
Who is Participating?
 
Ryan McCauleyData and Analytics ManagerCommented:
It's also possible that your maintenance plan contains a "Clean up history" task that's too aggressive. I used to confuse the "Clean up history" step (which removes SQL Agent job/task history over a certain age) and the "Maintenance Cleanup Task" step (which can be used to remove previous backup files over a certain age). If you're removing backups more than a day old but doing it with both types of step (or the wrong type), you'll see your job history gone with it.

Usually, we clean up backups after a few days, but don't clean up job history until it's older than 4-8 weeks.
0
 
ProjectChampionCommented:
In management studio go to "Management > Maintenance Plans" folder; find the pertinent maintenance plan, right-click and select view history.

If there's no history for a maintenance plan, which you are sure has run recently, then you're right something/someone has deleted the history.
0
 
data_bitsdbaAuthor Commented:
That's right, SSMS shows no history. How do I find what is deleting it and stopping it? Or at least not to be so extreme?
0
 
ProjectChampionCommented:
If you can't find any scheduled job that could have deleted the history then perhaps the maintenance plan hasn't recorded anything in the maintplan_logs.

The results generated by a maintenance plan can be either stored in the database (sysmaintplan_log and sysmaintplan_logdetail) or written in a report stored as a text file but - if I'm not mistaken - not both.

So I guess perhaps whoever created the plan has selected text report, which is why you can see any trace of it in the histories. If the results are written as text you can find the files under the SQL Server "LOG" folder on the server.
Also if you want to change the behavior of the job you can do so easily by editing the plan and changing the 'Reporting and Logging' settings.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.