Solved

Where has the SQL Server maintenance plan history gone to?

Posted on 2014-04-23
4
627 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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 I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

937 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

8 Experts available now in Live!

Get 1:1 Help Now