sp_purge_jobhistory (Transact-SQL)

SQL Server 2012

Removes the history records for a job.

Topic link icon Transact-SQL Syntax Conventions

Copy 
sp_purge_jobhistory 
   {   [ @job_name = ] 'job_name' | 
     | [ @job_id = ] job_id }
   [ , [ @oldest_date = ] oldest_date ]

[ @job_name= ] 'job_name'

The name of the job for which to delete the history records. job_nameis sysname, with a default of NULL. Either job_id or job_name must be specified, but both cannot be specified.

Note Note

Members of the sysadmin fixed server role or members of the SQLAgentOperatorRole fixed database role can execute sp_purge_jobhistory without specifying a job_name or job_id. When sysadmin users do not specify these arguments, the job history for all local and multiserver jobs is deleted within the time specified by oldest_date. When SQLAgentOperatorRole users do not specify these arguments, the job history for all local jobs is deleted within the time specified by oldest_date.
[ @job_id= ] job_id

The job identification number of the job for the records to be deleted. job_idis uniqueidentifier, with a default of NULL. Either job_id or job_name must be specified, but both cannot be specified. See the note in the description of @job_name for information about how sysadmin or SQLAgentOperatorRole users can use this argument.

[ @oldest_date = ] oldest_date

The oldest record to retain in the history. oldest_date is datetime, with a default of NULL. When oldest_date is specified, sp_purge_jobhistory only removes records that are older than the value specified.

0 (success) or 1 (failure)

When sp_purge_jobhistory completes successfully, a message is returned.

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.

Copy 
USE msdb ;
GO

EXEC dbo.sp_purge_jobhistory
    @job_name = N'NightlyBackups' ;
GO

B. Remove history for all jobs

Note Note

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.

Copy 
USE msdb ;
GO

EXEC dbo.sp_purge_jobhistory ;
GO
Show: