SqlTraceFlag: Unable to alter trace flags #1834

ekholve · 2023-01-16T14:27:03Z

Problem description

Hi,

I am using SqlTraceFlags to set the trace flags in the instance during the installation of the SQL Server. This seems to work fine when there are no trace flags present in the instance.

The problem is that now I am trying to write the tool which could be used to replace (or alter) the existing set of trace flags with the once desired. I have tried using TraceFlags and TraceFlagsToInclude with the similar results.

Code below is part of the script which works fine if there are no trace flags present in the instance but fails if any trace flags are already present. To my understanding below sample should simply replace any existing flags with the set of flags in the configuration (T3226 and T4199).

Thanks a lot for your assistance.

Kindest Regards,

Vesa

Verbose logs

VERBOSE: [REFDBC01N01]: LCM:  [ Start  Resource ]  [[SqlTraceFlag]SetTraceFlags]
VERBOSE: [REFDBC01N01]: LCM:  [ Start  Test     ]  [[SqlTraceFlag]SetTraceFlags]
VERBOSE: [REFDBC01N01]:                            [[SqlTraceFlag]SetTraceFlags] Determines the current state of the TraceFlags Compared to the desired TraceFlags 'INS01'.
VERBOSE: [REFDBC01N01]:                            [[SqlTraceFlag]SetTraceFlags] Found PowerShell module SQLPS already imported in the session. (SQLCOMMON0026)
VERBOSE: [REFDBC01N01]:                            [[SqlTraceFlag]SetTraceFlags] Get the current TraceFlags that are set on instance INS01.
Cannot convert argument "c", with value: "4199", for "AddRange" to type "System.Collections.ICollection": "Cannot convert the "4199" value of type "System.String" to type "System.Collections.ICollection"."
    + CategoryInfo          : NotSpecified: (:) [], CimException
    + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument
    + PSComputerName        : REFDBC01N01
 
VERBOSE: [REFDBC01N01]:                            [[SqlTraceFlag]SetTraceFlags] TraceFlag does not match the actual TraceFlags on the instance. Expected '4199,3226', but was '4199'.
VERBOSE: [REFDBC01N01]: LCM:  [ End    Test     ]  [[SqlTraceFlag]SetTraceFlags]  in 0.4270 seconds.

DSC configuration

SqlTraceFlag 'SetTraceFlags'
  {
      ServerName              = $Node.NodeName
      InstanceName            = $SqlInstanceName
      TraceFlags              = 3226,4199
      RestartService          = $true
  }

SQL Server edition and version

Microsoft SQL Server 2019 (RTM-CU12) (KB5004524) - 15.0.4153.1 (X64)   Jul 19 2021 15:37:34   Copyright (C) 2019 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

SQL Server PowerShell modules

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

Operating system

OsName               : Microsoft Windows Server 2019 Standard
OsOperatingSystemSKU : StandardServerEdition
OsArchitecture       : 64-bit
WindowsVersion       : 1809
WindowsBuildLabEx    : 17763.1.amd64fre.rs5_release.180914-1434
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

PowerShell version

Name                           Value
----                           -----
PSVersion                      5.1.17763.3770
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.17763.3770
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

SqlServerDsc version

Name         Version Path
----         ------- ----
SqlServerDsc 15.2.0  C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\15.2.0\SqlServerDsc.psd1

The text was updated successfully, but these errors were encountered:

johlju · 2023-01-16T17:11:51Z

This looks like a bug. We should add an integration tests to test this. Can you please check what the actual startup parameters (what the string look like) are on the Database Engine service so it is possible to reproduce the exact error?

ekholve · 2023-01-17T11:32:46Z

Thanks for your quick response.

As mentioned above, the script works fine if there are no existing trace flags present, but as soon as there are any, the script fails with the error I have given. And it makes no difference if the trace flag given in the script is already existing in the instance or completely new one.

The startup parameters currently in my lab system are:

ComputerName         : REFDBC01N01.ad.sqlrefe.fi
InstanceName         : INS01
SqlInstance          : REFDBC01N01\INS01
MasterData           : E:\INS01-SQLSYSTEM\MSSQL15.INS01\MSSQL\DATA\master.mdf
MasterLog            : E:\INS01-SQLSYSTEM\MSSQL15.INS01\MSSQL\DATA\mastlog.ldf
ErrorLog             : E:\INS01-SQLSYSTEM\MSSQL15.INS01\MSSQL\Log\ERRORLOG
TraceFlags           : {4199}
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       :
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      : -dE:\INS01-SQLSYSTEM\MSSQL15.INS01\MSSQL\DATA\master.mdf;-eE:\INS01-SQLSYSTEM\MSSQL15.INS01\MSSQL\Log\ERRORLOG;-lE:\INS01-SQLSYSTEM\MSSQL15.INS01\MSSQL\DATA\mastlog.ldf;-T4199

Hope this helps?

johlju · 2023-01-17T12:58:25Z

That helps, it was the property ParameterString I was interested in. I was looking for what "c" could be in the error string Cannot convert argument "c", with value: "4199. But the property ParameterString looks okay.

Could you try making the values string (and force it to array) to see if that works, or generates another error message?

SqlTraceFlag 'SetTraceFlags'
  {
      ServerName              = $Node.NodeName
      InstanceName            = $SqlInstanceName
      TraceFlags              = @('3226', '4199')
      RestartService          = $true
  }

johlju · 2023-01-17T12:58:31Z

Also, curious what you ran to get the output in your last comment - I'm seeing the property TraceFlags - wonder if it read-only in the object or if it possible to add TraceFlags to that property. Currently the resource changes the property ParameterString. Maybe there is a cleaner way of doing it. 🤔

ekholve · 2023-01-17T13:54:13Z

I was using dbatools, Get-StartupParameter cmdlet.
https://docs.dbatools.io/Get-DbaStartupParameter.html

Will try your suggestion shortly and reporting back.

ekholve · 2023-01-17T14:14:48Z

Tried your suggestion, but the exception is unfortunately the same.

Just thinking, but as there is currently just one value '4199' the exception seems to indicate reading the current value is the problem?

To make it maybe more readable for you, I have now changed the existing trace flag to 2371 and with the code below I am trying to replace 2371 with 3226 and 4199.

   SqlTraceFlag 'SetTraceFlags'
  {
      ServerName              = $Node.NodeName
      InstanceName            = $SqlInstanceName
      TraceFlags              = @('3226', '4199')
  }
 
 VERBOSE: [REFDBC01N01]: [[SqlTraceFlag]SetTraceFlags] Get the current TraceFlags that are set on instance INS01.
Cannot convert argument "c", with value: "2371", for "AddRange" to type "System.Collections.ICollection": "Cannot convert the "2371" value of type "System.String" to type "System.Collections.ICollection"."
    + CategoryInfo          : NotSpecified: (:) [], CimException
    + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument
    + PSComputerName        : REFDBC01N01
 
VERBOSE: [REFDBC01N01]: [[SqlTraceFlag]SetTraceFlags] TraceFlag does not match the actual TraceFlags on the instance. Expected '3226,4199', but was '2371'.
VERBOSE: [REFDBC01N01]: LCM:  [ End    Test     ]  [[SqlTraceFlag]SetTraceFlags]  in 0.4910 seconds.
The PowerShell DSC resource '[SqlTraceFlag]SetTraceFlags' with SourceInfo 'C:\Program Files\WindowsPowerShell\Modules\MyModuleName\functions\Set-RefSqlServerConfiguration.ps1::131::13::SqlTraceFlag' threw one or more non-terminating errors
 while running the Test-TargetResource functionality. These errors are logged to the ETW channel called Microsoft-Windows-DSC/Operational. Refer to this channel for more details.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : NonTerminatingErrorFromProvider
    + PSComputerName        : REFDBC01N01

Startup Parameters are now:

ComputerName         : REFDBC01N01.ad.sqlrefe.fi
InstanceName         : INS01
SqlInstance          : REFDBC01N01\INS01
MasterData           : E:\INS01-SQLSYSTEM\MSSQL15.INS01\MSSQL\DATA\master.mdf
MasterLog            : E:\INS01-SQLSYSTEM\MSSQL15.INS01\MSSQL\DATA\mastlog.ldf
ErrorLog             : E:\INS01-SQLSYSTEM\MSSQL15.INS01\MSSQL\Log\ERRORLOG
TraceFlags           : {2371}
CommandPromptStart   : False
MinimalStart         : False
MemoryToReserve      : 0
SingleUser           : False
SingleUserName       : 
NoLoggingToWinEvents : False
StartAsNamedInstance : False
DisableMonitoring    : False
IncreasedExtents     : False
ParameterString      : -dE:\INS01-SQLSYSTEM\MSSQL15.INS01\MSSQL\DATA\master.mdf;-eE:\INS01-SQLSYSTEM\MSSQL15.INS01\MSSQL\Log\ERRORLOG;-lE:\INS01-SQLSYSTEM\MSSQL15.INS01\MSSQL\DATA\mastlog.ldf;-T2371

johlju · 2023-01-17T14:30:43Z

I was using dbatools, Get-StartupParameter cmdlet.
https://docs.dbatools.io/Get-DbaStartupParameter.html

Thanks for the information! Looked at their code, and they parse it the same way the resource does.

the exception seems to indicate reading the current value is the problem?

You are correct. I wonder if it is Test-TargetResource that fails. It is the only place that I can see that can cause this error (that has AddRange()).

SqlServerDsc/source/DSCResources/DSC_SqlTraceFlag/DSC_SqlTraceFlag.psm1

Lines 382 to 392 in bed3b12

    
           if ($null -ne $getTargetResourceResult.TraceFlags) 
        
           { 
        
               $reference.AddRange($getTargetResourceResult.TraceFlags) 
        
           } 
        
           $difference = [System.Collections.ArrayList]::new() 
        
           if ($null -ne $TraceFlags) 
        
           { 
        
               $difference.AddRange($TraceFlags) 
        
           }

I would test to change this code to the following:

 if ($null -ne $getTargetResourceResult.TraceFlags) 
 { 
     $reference.AddRange(@($getTargetResourceResult.TraceFlags)) 
 } 
  
 $difference = [System.Collections.ArrayList]::new() 
  
 if ($null -ne $TraceFlags) 
 { 
     $difference.AddRange(@($TraceFlags))
 }

If it is a single current value we force it to an array.

I saw that there is no integration tests at all, that is probably why this bug slipped through. We should add an integration tests, I added an issue (#1835) to track that.

ekholve · 2023-01-17T14:34:03Z

Thanks a lot for your help, Johan. I will try the code you posted and report back. :)

johlju · 2023-01-17T14:37:16Z

Most likey the above is the error, looking att the method AddRange() we must pass an object that supports the interface ICollection: https://learn.microsoft.com/en-us/dotnet/api/system.collections.arraylist.addrange?view=netframework-4.8.1#system-collections-arraylist-addrange(system-collections-icollection)

Thanks a lot for your help, Johan. In case you need someone to test the new code, I can do that for you.

If you like, you can update the lines above in the file DSC_SqlTraceFlag.psm1 on the lab server, then run your configuration again. To verify that it works.

I can throw together and integration test this weekend. I had totally missed that we didn't have one.

ekholve · 2023-01-17T14:42:34Z

Yes, sorry I had a short circuit in my understanding. :D

The new code indeed seems to work without problems when using TraceFlags:

  SqlTraceFlag 'SetTraceFlags'
  {
      ServerName           = $Node.NodeName
      InstanceName        = $SqlInstanceName
      TraceFlags              = $TraceFlags
  }

VERBOSE: [REFDBC01N01]: LCM:  [ Start  Resource ]  [[SqlTraceFlag]SetTraceFlags]
VERBOSE: [REFDBC01N01]: LCM:  [ Start  Test     ]  [[SqlTraceFlag]SetTraceFlags]
VERBOSE: [REFDBC01N01]:                            [[SqlTraceFlag]SetTraceFlags] Determines the current state of the TraceFlags Compared to the desired TraceFlags 'INS01'.
VERBOSE: [REFDBC01N01]:                            [[SqlTraceFlag]SetTraceFlags] Found PowerShell module SQLPS already imported in the session. (SQLCOMMON0026)
VERBOSE: [REFDBC01N01]:                            [[SqlTraceFlag]SetTraceFlags] Get the current TraceFlags that are set on instance INS01.
VERBOSE: [REFDBC01N01]:                            [[SqlTraceFlag]SetTraceFlags] TraceFlag does not match the actual TraceFlags on the instance. Expected '3226,4199', but was '2371'.
VERBOSE: [REFDBC01N01]: LCM:  [ End    Test     ]  [[SqlTraceFlag]SetTraceFlags]  in 0.4900 seconds.
VERBOSE: [REFDBC01N01]: LCM:  [ Start  Set      ]  [[SqlTraceFlag]SetTraceFlags]
VERBOSE: [REFDBC01N01]:                            [[SqlTraceFlag]SetTraceFlags] Found PowerShell module SQLPS already imported in the session. (SQLCOMMON0026)
VERBOSE: [REFDBC01N01]:                            [[SqlTraceFlag]SetTraceFlags] Set the TraceFlags that are needed on instance INS01.
VERBOSE: [REFDBC01N01]: LCM:  [ End    Set      ]  [[SqlTraceFlag]SetTraceFlags]  in 0.9090 seconds.
VERBOSE: [REFDBC01N01]: LCM:  [ End    Resource ]  [[SqlTraceFlag]SetTraceFlags]

johlju · 2023-01-17T15:25:57Z

Awesome. If you like, you maybe send in a PR for that change, otherwise I will fix it when I add the integration test. 🙂

ekholve · 2023-01-17T15:42:11Z

Thanks Johan.

The TraceFlagsToInclude still seems to need some tuning as it throws the same exception as before. I can try something to address that if you have idea at hand? But that will happen tomorrow. ;)

Othwerise I believe you are able to fix that as well as soon as you have the integration test. I am in no hurry with this fix so no worries, take your time.

johlju · 2023-01-22T10:48:24Z

I didn't have time this weekend. I worked on another PR that took longer than expected. Try to get to this next weekend (or during the week if I have time).

- SqlServerDsc - The AppVeyor configuration file was updated to include the possibility to run skip installing one or more SQL Server instances when debugging in AppVeyor to help maximize the time alloted be run. - SqlTraceFlag - The resource is now tested with an integration tests (issue #1835). - A new parameter `ClearAllTraceFlags` was added so a configuration can enforce that there should be no trace flags. - The examples was updated to show that values should be passed as an array, even when there is only one value. - `Get-TargetResource` was updated to always return an array for parameter `TraceFlags`, `TraceFlagsToInclude`, and `TraceFlagsToInclude`. _The last_ _two properties will always return an empty array._ - `Set-TargetResource` was updated to handle a single trace flag in the current state (issue #1834). - `Set-TargetResource` was updated to correctly include or exclude a single flag (issue #1834).

johlju · 2023-02-05T10:48:52Z

The latest preview should now have a working SqlTraceFlag resource - the integration tests does work.

johlju added bug The issue is a bug. help wanted The issue is up for grabs for anyone in the community. good first issue The issue should be easier to fix and can be taken up by a beginner to learn to contribute on GitHub labels Jan 16, 2023

johlju changed the title ~~DSC_SqlTraceFlag: Unable to alter trace flags~~ SqlTraceFlag: Unable to alter trace flags Jan 16, 2023

johlju added in progress The issue is being actively worked on by someone. and removed help wanted The issue is up for grabs for anyone in the community. labels Jan 29, 2023

johlju mentioned this issue Jan 29, 2023

SqlTraceFlag: Add integration tests #1843

Merged

9 tasks

johlju closed this as completed in #1843 Feb 1, 2023

johlju removed in progress The issue is being actively worked on by someone. good first issue The issue should be easier to fix and can be taken up by a beginner to learn to contribute on GitHub labels Feb 5, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SqlTraceFlag: Unable to alter trace flags #1834

SqlTraceFlag: Unable to alter trace flags #1834

ekholve commented Jan 16, 2023

johlju commented Jan 16, 2023

ekholve commented Jan 17, 2023 •

edited

Loading

johlju commented Jan 17, 2023 •

edited

Loading

johlju commented Jan 17, 2023 •

edited

Loading

ekholve commented Jan 17, 2023 •

edited

Loading

ekholve commented Jan 17, 2023 •

edited

Loading

johlju commented Jan 17, 2023 •

edited

Loading

ekholve commented Jan 17, 2023 •

edited

Loading

johlju commented Jan 17, 2023

ekholve commented Jan 17, 2023 •

edited

Loading

johlju commented Jan 17, 2023

ekholve commented Jan 17, 2023

johlju commented Jan 22, 2023

johlju commented Feb 5, 2023

SqlTraceFlag: Unable to alter trace flags #1834

SqlTraceFlag: Unable to alter trace flags #1834

Comments

ekholve commented Jan 16, 2023

Problem description

Verbose logs

DSC configuration

Suggested solution

SQL Server edition and version

SQL Server PowerShell modules

Operating system

PowerShell version

SqlServerDsc version

johlju commented Jan 16, 2023

ekholve commented Jan 17, 2023 • edited Loading

johlju commented Jan 17, 2023 • edited Loading

johlju commented Jan 17, 2023 • edited Loading

ekholve commented Jan 17, 2023 • edited Loading

ekholve commented Jan 17, 2023 • edited Loading

johlju commented Jan 17, 2023 • edited Loading

ekholve commented Jan 17, 2023 • edited Loading

johlju commented Jan 17, 2023

ekholve commented Jan 17, 2023 • edited Loading

johlju commented Jan 17, 2023

ekholve commented Jan 17, 2023

johlju commented Jan 22, 2023

johlju commented Feb 5, 2023

ekholve commented Jan 17, 2023 •

edited

Loading

johlju commented Jan 17, 2023 •

edited

Loading

johlju commented Jan 17, 2023 •

edited

Loading

ekholve commented Jan 17, 2023 •

edited

Loading

ekholve commented Jan 17, 2023 •

edited

Loading

johlju commented Jan 17, 2023 •

edited

Loading

ekholve commented Jan 17, 2023 •

edited

Loading

ekholve commented Jan 17, 2023 •

edited

Loading