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?
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!