Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Removes the history records for a job.
Applies to: SQL Server (SQL Server 2008 through current version).
By default, only members of the sysadmin fixed server role or the SQLAgentOperatorRole fixed database role can execute this stored procedure. Members of sysadmin can purge the job history for all local and multiserver jobs. Members of SQLAgentOperatorRole can purge the job history for all local jobs only.
Other users, including members of SQLAgentUserRole and members of SQLAgentReaderRole, must explicitly be granted the EXECUTE permission on sp_purge_jobhistory. After being granted EXECUTE permission on this stored procedure, these users can only purge the history for jobs that they own.
The SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole fixed database roles are in the msdb database. For details about their permissions, see SQL Server Agent Fixed Database Roles.
A. Remove history for a specific job
The following example removes the history for a job named NightlyBackups.
USE msdb ; GO EXEC dbo.sp_purge_jobhistory @job_name = N'NightlyBackups' ; GO
B. Remove history for all jobs
Only members of the sysadmin fixed server role and members of the SQLAgentOperatorRole can remove history for all jobs. When sysadmin users execute this stored procedure with no parameters, the job history for all local and multiserver jobs is purged. When SQLAgentOperatorRole users execute this stored procedure with no parameters, only the job history for all local jobs is purged.
The following example executes the procedure with no parameters to remove all history records.
USE msdb ; GO EXEC dbo.sp_purge_jobhistory ; GO