sp_purge_jobhistory (Transact-SQL)
Applies to: SQL Server Azure SQL Managed Instance
Removes the history records for a job in the SQL Server Agent service.
Transact-SQL syntax conventions
Syntax
sp_purge_jobhistory
{ [ @job_name = ] 'job_name' |
| [ @job_id = ] job_id }
[ , [ @oldest_date = ] oldest_date ]
Arguments
@job_name
The name of the job for which to delete the history records. job_name is 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
The job identification number of the job for the records to be deleted. job_id is 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
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.
This stored procedure shares the name of sp_purge_jobhistory
with a similar object for the Azure Elastic Jobs service for Azure SQL Database. For information about the elastic jobs version, see jobs.sp_purge_jobhistory (Azure Elastic Jobs) (Transact-SQL).
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
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.
USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory ;
GO
Next steps
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for