Sample Header Ad - 728x90

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

2 votes
1 answer
240 views
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. enter image description here 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 @job_name=@job_name there is basically one for each step in the job as you can see on the picture below: enter image description here =================================== 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.
Asked by Marcello Miorelli (17274 rep)
May 13, 2021, 05:02 PM
Last activity: May 16, 2021, 11:07 AM