Solved

Where has the SQL Server maintenance plan history gone to?

Posted on 2014-04-23
4
610 Views
Last Modified: 2014-04-30
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!
0
Comment
Question by:data_bits
  • 2
4 Comments
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40019446
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
 

Author Comment

by:data_bits
ID: 40019801
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
 
LVL 8

Assisted Solution

by:ProjectChampion
ProjectChampion earned 200 total points
ID: 40019845
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
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 300 total points
ID: 40020815
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now