sp_delete_jobsteplog

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.

USE msdb
GO

DECLARE @job nvarchar(128)
DECLARE @sql1 VARCHAR(max)
Declare @sql2 VARCHAR (MAX)
Declare @sql3 VARCHAR (MAX)
DECLARE @DaysToKeepHistory DATETIME

DECLARE jobnames CURSOR FOR 
		SELECT name from dbo.sysjobs (NOLOCK)
SET @DaysToKeepHistory =  (SELECT GETDATE() - 30)
OPEN jobnames  
FETCH NEXT FROM jobnames INTO @job  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @sql1 = 'EXEC [dbo].[sp_delete_jobsteplog]      @job_name  = ' + CHAR (39) +  @job  + CHAR (39) 
	   SET @sql2  = ' ,  @older_than = ' + CHAR(39) +  CONVERT (CHAR (10), @DaysToKeepHistory, 101)  + CHAR(39) 
	   SET @sql3 = @sql1  +   @sql2 + ' ; ' 
       --PRINT @sql3
       EXEC (@sql3)
     
       FETCH NEXT FROM jobnames INTO @job  
END  

CLOSE jobnames  
DEALLOCATE jobnames