How to trim the log file that stores information about job history execution?

All we need is an easy explanation of the problem, so here it is.

I was having a look at my sql server jobs and proxies on an specific server in the production environment.

I pretty much have an script to check the status of the jobs but in case I have to troubleshoot why a job failed on this particular server it has been using the following settings:

  • log to table
  • append output to existing entry in table
  • include step output in history

But no file has been specified, so when I click on View to check that has been going on, I get and out of memory exception, as the table is too big.

How to trim the log file that stores information about job history execution?

the way I find out the size of this log file (which I am not sure where is stored):

    use msdb 
    go
    
    declare @job_id    UNIQUEIDENTIFIER 
           ,@job_name  sysname   
    
    select @job_name= N'DBA - my job name'
    
    exec sp_help_jobsteplog @[email protected]_name

there is basically one for each step in the job as you can see on the picture below:

How to trim the log file that stores information about job history execution?

===================================

Exception of type ‘System.OutOfMemoryException‘ was thrown. (Microsoft.SqlServer.Smo)


Program Location:

at Microsoft.SqlServer.Management.Smo.Agent.JobStep.EnumLogs()
at Microsoft.SqlServer.Management.SqlManagerUI.JobStepAdvancedLogging.ReadStepLogToFile(JobStep step)
at Microsoft.SqlServer.Management.SqlManagerUI.JobStepAdvancedLogging.viewTableLog_Click(Object sender, EventArgs e)

TITLE: Microsoft SQL Server Management Studio

Exception of type ‘System.OutOfMemoryException’ was thrown. (Microsoft.SqlServer.Smo)


BUTTONS:

OK

My question would be:

How do I trim this log file ?

I know I could simply set the long to an external text file, but that would only be my second option.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The information is not in a file, it is a table named sysjobstepslogs. The column for the logged data is nvarchar(max). I don’t think there’s any trunc option for this table, so if you output lots and lots of data from a job step, then you will have to live with having the last execution’s data in this table being huge.

Note, that the regular sysjobhistory table (the one that the history GUI in SSMS uses) is capped at about 4000 characters and it doesn’t include result from SELECT (it only includes output from errors and PRINT).

This raises the question why you have this huge amount of output from some of your jobsteps? Are you doing unnecessary SELECT in the job?

In case you can’t trim down the output from the jobstep, then I would suggest that you just don’t look at the data using the SSMS GUI since it bombs on that amount of output. This can be considered a bug, so report it to MS if you want. Anyhow, you can do SELECT from the sysjobstepslogs table directly and if SSMS chokes on the amount of data returned, then just do some substring to cap the length.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply