I have multiple Sql Agent jobs, with multiple steps each. For a large subset of these jobs, I want to set the logging levels to PERFORMANCE instead of BASIC - the logs are taking a lot of log space, and, more importantly, are causing a slowdown for other processes.

I went into SysJobSteps, but there is no column that I can update. I am considering changing hundreds jobs so doing this manually isn't an option for me.

How do I do this using TSQL?

Here is an image of the 4 options available:

enter image description here


The logging level is held in the command column of msdb.dbo.sysjobsteps. By editing the command you can set the logging level you require. There is a system stored procedure - sp_update_jobstep - to achieve this.

From a quick look on my local system, SSIS job steps' commands seem to be a reasonably straight-forward concatenation of labeled values. A bit of regex and some substitutions should be sufficient.

  • Ah, just a bit of Regex. PowerShell should do the trick. – Erik Darling Nov 2 '17 at 5:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.