Script to Remove Jobs History.
You can remove jobs history for particular job for a fixed time.
Below script will remove history data older then 30 days and where job name is “jobname”.
I have used procedure “SP_PURGE_JOBHISTORY” provided by SQL Server, you just need to pass @JobName and @Date as a parameter.
DECLARE @Date DATETIME DECLARE @Jobname VARCHAR(256) -- Keep Last 30 days SET @Date = GETDATE() - 30 SET @Jobname = 'JobName' EXEC MSDB.DBO.SP_PURGE_JOBHISTORY @Job_name=@Jobname, @Oldest_date=@Date
You can also pass JobID instead of JobName.
DECLARE @Date DATETIME DECLARE @JobID VARCHAR(256) -- Keep Last 30 days SET @Date = GETDATE() - 30 SET @Job_Id = 'JobID' EXEC MSDB.DBO.SP_PURGE_JOBHISTORY @Job_ID=@Job_ID, @Oldest_date=@Date
If you want to remove all history for particular job then remove @date from parameter.
DECLARE @JobID VARCHAR(256) SET @Job_Id = 'JobID' EXEC MSDB.DBO.SP_PURGE_JOBHISTORY @Job_name=@Jobname
If you want to clear all history for all jobs just run the procedure “EXEC MSDB.DBO.SP_PURGE_JOBHISTORY”.
it will remove all history for you instance.
SQL Server has two ways to remove data older then 30 days for all jobs.
1) By Script.
DECLARE @Date DATETIME -- Keep Last 30 days SET @Date = GETDATE() - 30 SET @Jobname = 'JobName' EXEC MSDB.DBO.SP_PURGE_JOBHISTORY @Oldest_date=@Date
2) By SQL Server GUI.
Step1 : Right click on SQL Server Agent and click on property.
Step2: On the left pane select History and click check box remove agent history.
you have three options day(s), week(s) or month(s) select one of appropriate to remove agent history.
Regards,
Nirav gajjar