Solved

Where has the SQL Server maintenance plan history gone to?

Posted on 2014-04-23
4
777 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

617 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