sp_delete_jobsteplog (Transact-SQL)

Article
05/16/2013

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.

Topic link icon Transact-SQL Syntax Conventions

Syntax

sp_delete_jobsteplog { [ @job_id = ] 'job_id' | [ @job_name = ] 'job_name' }
       [ , [ @step_id = ] step_id | [ @step_name = ] 'step_name' ]
       [ , [ @older_than = ] 'date' ]
       [ , [ @larger_than = ] 'size_in_bytes' ]

Arguments

[ @job_id =] 'job_id'
The job identification number for the job that contains the job step log to be removed. job_id is int, with a default of NULL.
[ @job_name =] 'job_name'
The name of the job. job_name is sysname, with a default of NULL.

Note

Either job_id or job_name must be specified, but both cannot 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. If not included, all job step logs in the job are deleted unless @older_than or @larger_than are specified. step_id is int, with a default of NULL.
[ @step_name =] '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.

Note

Either step_id or step_name can be specified, but both cannot be specified.
[ @older_than =] 'date'
The date and time of the oldest job step log you want to keep. All job step logs that are older than this date and time are removed. date is datetime, with a default of NULL. Both @older_than and @larger_than can be specified.
[ @larger_than =] 'size_in_bytes'
The size in bytes of the largest job step log you want to keep. All job step logs that are larger that this size are removed. Both @larger_than and @older_than can be specified.

Return Code Values

0 (success) or 1 (failure)

Result Sets

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

By default, members of the sysadmin fixed server role can execute this stored procedure. 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. Removing 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. Removing 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. Removing 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

Share via