Delete job history for a SQL Server Job

Easiest option to delete history of a job is from GUI of SQL Server. Please refer to below screen print to find how to delete history of a specific job:

This screen is found at below path:

SQL Server Agent -> Jobs - > Select Job and then right click and open View History

However using this option can be resource intensive and tedious for production environment. Hence I prefer to execute sp_purge_jobhistory procedure to delete job history from SQL Server:

Syntax:

sp_purge_jobhistory 

   {   [ @job_name= ] 'job_name' | 

     | [ @job_id= ] job_id }

   [ , [ @oldest_date = ] oldest_date ]

Note: You can specify either job name or job id. Also oldest_date is the oldest record which will be in the history. This means if you mention oldest_date is 10-1-2010, this procedure will delete all history for the job which is older than 10-1-2010

Example:

To delete all history for a specific job named “Test”

USE msdb ;

GO

EXEC dbo.sp_purge_jobhistory

@job_name = N'Test' ;

To delete all history for a specific job named “Test” up to 10-1-2010

USE msdb ;

GO

EXEC dbo.sp_purge_jobhistory

@job_name = N'Test' ,

@oldest_date = '10-1-2010'

To remove all history for all jobs

USE msdb ;

GO

EXEC dbo.sp_purge_jobhistory ;

GO