Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Where has the SQL Server maintenance plan history gone to?

Posted on 2014-04-23
4
Medium Priority
?
894 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 800 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 1200 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

886 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