sp_purge_jobhistory (Transact-SQL)

Removes the history records for a job.

Transact-SQL Syntax Conventions

Syntax

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

Arguments

• [ @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

  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.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

When sp_purge_jobhistory completes successfully, a message is returned.

Permissions

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.

Examples

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

The following example executes the procedure with no parameters to remove all history records.

USE msdb ;
GO

EXEC dbo.sp_purge_jobhistory ;
GO

See Also

Reference

sp_help_job (Transact-SQL)

sp_help_jobhistory (Transact-SQL)

System Stored Procedures (Transact-SQL)

GRANT Object Permissions (Transact-SQL)