Edit

Share via

sp_delete_jobsteplog (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Managed Instance

Removes all SQL Server Agent job step logs that are specified with the arguments. Use this stored procedure to maintain the sysjobstepslogs table in the msdb database.

Transact-SQL syntax conventions

Syntax

sp_delete_jobsteplog
    [ [ @job_id = ] 'job_id' ]
    [ , [ @job_name = ] N'job_name' ]
    [ , [ @step_id = ] step_id ]
    [ , [ @step_name = ] N'step_name' ]
    [ , [ @older_than = ] older_than ]
    [ , [ @larger_than = ] larger_than ]
[ ; ]

Arguments

[ @job_id = ] 'job_id'

The job identification number for the job that contains the job step log to be removed. @job_id is uniqueidentifier, with a default of NULL.

Either @job_id or @job_name must be specified, but both can't be specified.

[ @job_name = ] N'job_name'

The name of the job. @job_name is sysname, with a default of NULL.

Either @job_id or @job_name must be specified, but both can't be specified.

[ @step_id = ] step_id

The identification number of the step in the job for which the job step log is to be deleted. @step_id is int, with a default of NULL. If not included, all job step logs in the job are deleted unless @older_than or @larger_than are specified.

Either @step_id or @step_name can be specified, but both can't be specified.

[ @step_name = ] N'step_name'

The name of the step in the job for which the job step log is to be deleted. @step_name is sysname, with a default of NULL.

Either @step_id or @step_name can be specified, but both can't be specified.

[ @older_than = ] older_than

The date and time of the oldest job step log you want to keep. @older_than is datetime, with a default of NULL. All job step logs that are older than this date and time are removed.

Both @older_than and @larger_than can be specified.

[ @larger_than = ] larger_than

The size in bytes of the largest job step log you want to keep. @larger_than is int, with a default of NULL. All job step logs that are larger that this size are removed.

Both @older_than and @larger_than can be specified.

Return code values

0 (success) or 1 (failure).

Result set

None.

Remarks

sp_delete_jobsteplog is in the msdb database.

If no arguments except @job_id or @job_name are specified, all job step logs for the specified job are deleted.

Permissions

This stored procedure is owned by the db_owner role. You can grant EXECUTE permissions for any user, but these permissions may be overridden during a SQL Server upgrade.

Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

Only members of sysadmin can delete a job step log that is owned by another user.

Examples

A. Remove all job step logs from a job

The following example removes all job step logs for the job Weekly Sales Data Backup.

USE msdb;
GO

EXEC dbo.sp_delete_jobsteplog
    @job_name = N'Weekly Sales Data Backup';
GO

B. Remove the job step log for a particular job step

The following example removes the job step log for step 2 in the job Weekly Sales Data Backup.

USE msdb;
GO

EXEC dbo.sp_delete_jobsteplog
    @job_name = N'Weekly Sales Data Backup',
    @step_id = 2;
GO

C. Remove all job step logs based on age and size

The following example removes all job steps logs that are older than noon October 25, 2005 and larger than 100 megabytes (MB) from the job Weekly Sales Data Backup.

USE msdb;
GO

EXEC dbo.sp_delete_jobsteplog
    @job_name = N'Weekly Sales Data Backup',
    @older_than = '10/25/2005 12:00:00',
    @larger_than = 104857600;
GO

Related content