New job, new house, new year...lots of changes within the last 12 months. What hasn't changed is my love of working with SQL Server. And my annoyance with certain things.
Latest: The MSDB on a server has grown immense. What the heck is going on? So I took a look, it turns out that the MSDB backup job history has never been cleaned up. Due to the high volume of databases on this particular server combined with tlog backups every 15 minutes it bloated the MSDB database...like ridiculously, 15 GB.
Now there's a fairly easy fix for this, you could use sp_delete_backuphistory to help clean up the MSDB database. One thing though, I would recommend scheduling it to occur after hours because with an MSDB of this size it may take a while and could potentially cause performance issues. And increasing the size of the drive would only be a temporary solution, the real problem is that the MSDB backup history is not being cleaned up.
Why isn't there a default cleanup job in SQL set for, say, every 45 days? We clean up Agent error log files, why not the MSDB history?
Schedule this cleanup to delete entries older than a certain (rolling) date and you've got yourself a permanent fix.
No comments:
Post a Comment