I've run into a 'suspended job step' agent error a few times when running multiple powershell jobs andsteps at the same time. I looked in msdb.dbo.syssubsystems and the powershell subsystem is only allowed to run 2 threads by default.

Can anyone clarify if it's ok to update this to a greater value (and if this alone, is the only place to update) so that SQLAgent can run more than two ps jobsteps at a time? I've currently updated a test system to a value of 3 and will be watching the error logs. Here's to hoping....

I assume, that it's a low value in order to avoid the psh subsystem from overrunning cache memory - or something along those lines. I know that some of the ps scripts I've written can be large-ish memory objects when executed, so I presume that MS is trying to protect us from ourselves (which is often nice).

It's been a few days and since updating the value from 2 to 3 threads my jobs are not queuing up anymore and the server seems to be stable.

I would still like to know why the powershell subsystem under SQLAgent has such a limited/low max_worker_threads value though. CmdExec & ActiveScriptingfor instancehave 80 for thier max_worker_threads values.

Beuller?

Updating syssubsystems table in msdb is unsupported.  Let us know your use cases and the the scenarios.

If you would like this setting to be changed / user configurable in future releases, please file a feature request at https://connect.microsoft.com/SQLServer/Feedback 

Thanks

Sethu Srinivasan [MSFT]

SQL Server

http://blogs.msdn.com/sqlagent

We have addressed this issue by allowing customers to update "max_worker_threads " in  msdb's syssubsystems table. Fix is available in SQL 2016 CTP 2.1

Link: http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

If you need this fix for specific version of SQL Server, Please file a request with Microsoft Customer Support.

Thanks