SqlConfiguration - 'blocked process threshold' option support

waldeqKW commented 1 year ago

Problem description

Setting 'blocked process threshold' option using SqlConfiguration gives error: PowerShell DSC resource DSC_SqlConfiguration failed to execute Test-TargetResource functionality with error message: S pecified option 'blocked process threshold' could not be found.

Verbose logs

VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' =SendConfigurationApply,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' =                                root/Microsoft/Windows/DesiredStateConfiguration'.
VERBOSE: An LCM method call arrived from computer SOURCE-TEST with user sid                                         SID.                                                                        
VERBOSE: [DESTINATION-TEST]: LCM:  [ Start  Set      ]                                                                    
VERBOSE: [DESTINATION-TEST]: LCM:  [ Start  Resource ]  [[SqlConfiguration]BlockedProcessthreshold]                       
VERBOSE: [DESTINATION-TEST]: LCM:  [ Start  Test     ]  [[SqlConfiguration]BlockedProcessthreshold]                       
VERBOSE: [DESTINATION-TEST]:                            [[SqlConfiguration]BlockedProcessthreshold] Connecting as current  user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)
VERBOSE: [DESTINATION-TEST]:                            [[SqlConfiguration]BlockedProcessthreshold] Connected to SQL
instance 'DESTINATION-TEST'. (SQLCOMMON0018)
VERBOSE: [DESTINATION-TEST]: LCM:  [ End    Test     ]  [[SqlConfiguration]BlockedProcessthreshold]  in 1.4680 seconds.
PowerShell DSC resource DSC_SqlConfiguration  failed to execute Test-TargetResource functionality with error message: S
pecified option 'blocked process threshold' could not be found.
Parameter name: OptionName
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : DESTINATION-TEST

VERBOSE: [DESTINATION-TEST]: LCM:  [ End    Set      ]
The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : DESTINATION-TEST

VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 1.927 seconds

DSC configuration

Configuration example
{
    Import-DscResource -ModuleName SqlServerDsc
    node localhost
    {
        SqlConfiguration 'BlockedProcessthreshold'
        {
            ServerName = 'localhost'
            InstanceName = 'MSSQLSERVER'
            OptionName = 'blocked process threshold'
            OptionValue = 5
        }
    }
}

SQL Server edition and version

Microsoft SQL Server 2019 (RTM-CU20) (KB5024276) - 15.0.4312.2 (X64)   Apr  1 2023 12:10:46   Copyright (C) 2019 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2022 Datacenter 10.0 <X64> (Build 20348: ) (Hypervisor)

SQL Server PowerShell modules

Name  Version Path
----  ------- ----
SQLPS 15.0    E:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

Operating system

OsName               : Microsoft Windows Server 2022 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture       : 64-bit
WindowsVersion       : 2009
WindowsBuildLabEx    : 20348.1.amd64fre.fe_release.210507-1500
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

PowerShell version

5.1.20348.1366

SqlServerDsc version

16.1.0

johlju commented 1 year ago

The display name of the option is blocked process threshold (s).

johlju commented 1 year ago

I added a new command to PR #1935 that will help list the available configuration option names (once merged).

$serverObject = Connect-SqlDscDatabaseEngine -InstanceName SQL2022
$serverObject | Get-SqlDscConfigurationOption | ft

johlju commented 1 year ago

Closing this as resolved. Reopen if there are still an issue.

dsccommunity / SqlServerDsc