2

I have a maintenance plan set up on our SQL Server 2012 that contains only a "History Cleanup Task", which is configured to delete the following data if older than 4 weeks:

  • Backup and restore history
  • SQL Server Agent job history
  • Maintenance plan history

It runs every day at 3:00 a.m. without error, however I've only just noticed that it doesn't appear to be deleting records from the backupset table and the job completes in 0 seconds.

How can I fix it, or get more info about what's going wrong?

UPDATE

Thanks for the response but from some of the comments it appears people think I am having trouble deleting old backup files. I am actually trying to clean up the history data of old backup job execution, which I understand lives in the msdb database.

The reason I want to is that we've encountered long delays and ballooning server resource usage just when trying to drop a database, as a consequence of many years of backup history sitting in msdb.

Here is a good analysis of the problem: https://sqlserver-help.com/2014/07/10/troubleshooting-slow-delete-database-from-management-studio/

The last line of the blog post is:

If you clean msdb backup history regularly, you might not face the issue though. There is maintenance plan to do that. Try it out!

And this is what I'm attempting to do now!

Improve this question
4
  • 3
    Please see this answer. There are better ways to manage this without using maintenance plan. Such as Ola Hallengren's solution.
    – SqlWorldWide
    Commented Jul 6, 2017 at 12:41
  • what are the extensions of your backups? are you running winrar on them? try to send the info messages to a log txt file, and then we can help you to analyze it.
    – Racer SQL
    Commented Jul 6, 2017 at 18:30
  • Was your issue resolved or are you still looking for a solution?
    – John K. N.
    Commented Jun 26, 2018 at 5:49
  • It was a long time ago now so my memory is failing me. No longer looking for a solution as I've left that role. We may have gone with something a little more aggressive like calling the cleanup stored proc directly from an agent job or perhaps simply migrating to a later version of SQL without the issue. Sorry for the vague comment.
    – Michael12345
    Commented Jul 26, 2018 at 4:11

1 Answer 1

Reset to default
1

In the Maintenance plan job, create an output file to see what exactly is going on during the job run. Mostly there is something missing. Drop and recreate the plan and test.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.