Is your msdb database growing huge? Do you need to clean up a lot of job history as well as database mail log?

MSDB database has a log of all the job history and also the database mail that can keep on growing and this is one thing a DBA team should be avoiding.

Below is a quick script that erases everything older than 90 days and I schedule it to run every week. This deletes all the logs older than 90 days, which is more than enough of log information unless one has a specific reason to have more information logged all the time.

 DECLARE @deletebeforedate datetime
SET @deletebeforedate = DATEADD(dd, -90, GETDATE())
EXEC msdb.dbo.sp_delete_backuphistory @deletebeforedate
EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date = @deletebeforedate
EXEC msdb..sp_maintplan_delete_log null,null,@deletebeforedate
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @deletebeforedate
EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @deletebeforedate

  

Delete files older than certain number of days using a windows batch file

Same old problem keeps repeating at every client place! The customized backup jobs sometimes  fail to clean up the files older than 2 days (or whatever the threshold is) and the backup jobs fail eventually when the drive gets full, One smart and quick solution for this is to use the 'forfiles' utility and schedule it as a task in windows or use xp_cmdshell to schedule the same as a SQL server agent job. The command looks something like the below:

forfiles /p D:\backup\ /s /m *.trn /d -2 /c "cmd /c del @file"

Change the extension from .trn to .bak if you  are trying to cleanup full backup files instead of transaction log files.