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!
Best Answer
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.