housekeeping of the MSDB

Dear all,

AS the MSDB keep all the:

1)Backup and restore history,
2)SQL server agent job history.
3)Maintenance plan history.

and if we do not purge that regularily, the backup task will run longer and longer.

Any script to run on how many days of all of the above kept so that we know how many days only history we should keep?

marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
Ryan McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
We do this cleanup as a step in our Maintenance plans, not as a separate job, but there's no reason you couldn't. Also, the amount of data generated is relatively small - while I'm not saying it's a good idea to never maintain it, it's small compared to the logging data generated elsewhere on your server, and I've never seen a case (even on servers that aren't maintained at all) of a large MSDB or out of control agent logs slowing down job execution.

Maybe I'm sheltered, but I wouldn't put too much stress on this - adding a "Maintenance Plan Cleanup" task, as mentioned above, should accomplish what you're looking for, even if you keep a few months worth of logs.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Anthony PerkinsConnect With a Mentor Commented:
1)  sp_delete_backuphistory
marrowyungSenior Technical architecture (Data)Author Commented:
let me check and get back to you all soon.
marrowyungSenior Technical architecture (Data)Author Commented:
we don't consider this option right now ! it should be the housekeeping job for the rest of the DB instead of the MSDB one.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.