Maintaining the SQL Server msdb size should be a regular DBA task. The exact tasks to complete , depend on how you use SQL server
A typical question I receive is:
“The MSDB database has grown till 10 GB and now I'm experiencing no more space on disk C:.”
Note: There are different situations where you won’t want to delete history. Plan ahead – and only apply these procedures in accordance with system policies
Exceute SP_DELETE_BACKUPHISTORY
A system stored procedure . Maintains the size of backup and restore history tables.
It takes one argument : @oldest_date
Execute SP_PURGE_JOBHISTORY
A system stored procedure. The option of deleting all job history records or the history of one job
Execute SP_MAINTPLAN_DELETE_LOG
Clears up the Maintenance Plan History . This undocumented store procedure is the equivalent of the “Delete” button on the Maintenance GUI
Possible scenarios include:
1: User wants to delete all logs
2: User wants to delete all logs older than X date
3: User wants to delete all logs for a given plan
4: User wants to delete all logs for a specific subplan
5: User wants to delete all logs for a given plan older than X date
6: User wants to delete all logs for a specific subplan older than X date
Source:Jack Vamvas (http://www.sqlserver-dba.com)
