is it possible to trim the table output for a specific job?

Question

I have a routine to save the logins and apply them on a different server , and I run this routine from a job that I have scheduled to run everyday.

On that job I have specified these 3 things:

1. Log to table
2. Append output to existing entry in table
3. Include step output in history

as can be seen on the picture below:

I am happy with that, however, I was wondering if there is a way to trim the table output because when I open it I see records from over 4 months ago, which are no longer relevant now, as shown on the picture below:

when I click View:

I get:

and the table has over 61 thousand lines.

Every time the job runs it adds about 600 new lines to the bottom of the table.

Can I keep the last let's say 10 thousand lines only,getting rid of the older lines?

Answer 1

Yes.

The CommandLog cleanup is from Ola Hallengren's Maintenance Solution, so doesn't affect msdb history directly.

Use sp_purge_jobhistory, like so:

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

Per the documentation, 'job_name' is the name of the job for which to delete the history records. Either job_id or job_name must be specified, but both cannot be specified.

Answer 2

this is a partial solution - as I could not find out how to just trim the table log.

using sp_purge_jobhistory

DECLARE @DAY DATETIME = DATEADD( dd,-30,getdate())
exec msdb.dbo.sp_purge_jobhistory   
   @job_name = 'DBA - Save the JOBS and LOGINS'
 , @oldest_date = @DAY

that trims the jobhistory not the table log

what is supposed to trim that table log is sp_delete_jobsteplog

this is suppose to delete all records in that table - and it does:

DECLARE @DAY DATETIME = DATEADD( dd,-30,getdate())
PRINT @DAY
exec msdb.dbo.sp_delete_jobsteplog 
   @job_name = 'DBA - Save the JOBS and LOGINS',
   @step_name = 'SAVE THE LOGINS'

this is the original situation:

sp_help_jobsteplog shows who is actually the log table and where it lives and how big it is

exec msdb.dbo.sp_help_jobsteplog
   @job_name = 'DBA - Save the JOBS and LOGINS',
   @step_name = 'SAVE THE JOBS'

this does not work as I would expect it to:

DECLARE @DAY DATETIME = DATEADD( dd,-30,getdate())
PRINT @DAY
exec msdb.dbo.sp_delete_jobsteplog 
   @job_name = 'DBA - Save the JOBS and LOGINS',
   @step_name = 'SAVE THE JOBS',
   @older_than = @day,
   @larger_than = 52428800  

this does delete all records - not exactly what I wanted:

exec msdb.dbo.sp_delete_jobsteplog 
   @job_name = 'DBA - Save the JOBS and LOGINS',
   @step_name = 'SAVE THE JOBS',
   --@older_than = @day,
   @larger_than = 52428800  

I think I found out why Parameter in sp_delete_jobsteplog don’t work

Here is how I understand the function of sp_delete_jobsteplog.

The sysjobstepslogs table has one row for each job/step that you are logging. Every time the job runs, it concatenates the new log to the end of the “log” column for that step. Multiple logs are all stored as one concatenated VARCHAR(MAX).

When you run sp_delete_jobsteplog with the @older_than parameter, it does not parse through the “log” column and remove entries older than you want, as you might expect. What it does is looks at the “date_modified” column and deletes the entire row if that date is older than @older_than. So if the job runs once per hour and you want to delete everything older than 3 days, it will never delete anything because the “date_modified” will always be less than an hour old.

Here is the code from inside sp_delete_jobsteplog (SQL Server 2012 SP3).

DELETE FROM msdb.dbo.sysjobstepslogs
 WHERE (step_uid IN (SELECT DISTINCT step_uid 
        FROM msdb.dbo.sysjobsteps js, msdb.dbo.sysjobs_view jv
        WHERE (  @job_id = jv.job_id )
          AND (js.job_id = jv.job_id )
          AND ((@step_id IS NULL) OR (@step_id = step_id)))) 
  AND ((@older_than IS NULL) OR (date_modified < @older_than))
  AND ((@larger_than IS NULL) OR (log_size > @larger_than))