Sql-server – PowerShell Subsystem max_worker_threads reverts back to default after applying cumulative update for SQL Server 2016

powershellsql-server-2016

We change the PowerShell subsystem max_worker threads to 10, but after we install the latest cumulative update, it reverts back to 2. Is there a way to keep that from happening?

SELECT  s.subsystem_id, 
        s.subsystem, 
        m.[text] AS [description], 
        s.max_worker_threads
FROM msdb.dbo.syssubsystems s
INNER JOIN msdb.sys.messages m ON s.description_id = m.message_id 
AND m.language_id = 1033

Best Answer

Create an agent job that runs on start up to set the number of threads. Set the schedule option to Start Automatically when SQL server agent starts.

Related Solutions

SQL Server Job – Where Does PowerShell ‘Log to Table’ Save Logs

A simple press on the F1 key for the help would reveal, that the execution result is stored in the sysjobstepslogs table of the msdb database.

Sql-server – How to enable both PowerShell Remoting and SPN for SQL Server Reporting

I believe we have found the solution. To avoid Reporting Services and WinRM fighting over the HTTP SPN, you can set a port-specific SPN for the WinRM like this:

setspn -S HTTP/<Machine>:<port> <Machine>

It's a good idea to create SPNs for both short machine name and the FQDN.
The default port is 5985 for HTTP and 5986 for HTTPS, but I believe it can be set up to use different ports.

When using WinRM, I just set up a session like this:

$CimSessionOption = New-CimSessionOption -EncodePortInServicePrincipalName
$CimSession = New-CimSession -Name ServiceSession -SessionOption $CimSessionOption -ComputerName <Machine>
Get-CimInstance Win32_Service -CimSession $CimSession

Best Answer

Related Solutions

SQL Server Job – Where Does PowerShell ‘Log to Table’ Save Logs

Sql-server – How to enable both PowerShell Remoting and SPN for SQL Server Reporting

Related Question