It’s been a few weeks since i have blogged as I have been busy with a lot of other things. One of which is preparing for my SQL Pass Summit pre-con which has lead to me improving the CI/CD for dbachecks by adding auto-creation of online documentation, which you can find at https://dbachecks.readthedocs.io or by running Get-Help with the -Online switch for any dbachecks command.
Get-Help Invoke-DbcCheck -Online
I will blog about how dbachecks uses Azure DevOps to do this another time
PSPowerHour
The PowerShell community members Michael T Lombardi and Warren Frame have created PSPowerHour. PSPowerHour is “like a virtual User Group, with a lightning-demo format, and room for non-PowerShell-specific content. Eight community members will give a demo each PowerHour.”
Chrissy blogged about the first one on the dbatools blog
You can watch the videos on the Youtube channel and keep an eye out for more online PSPowerHours via twitter or the GitHub page.
While watching the first group of sessions Andrew Wickham demonstrated using dbatools with trace flags and I thought that needs to be added to dbachecks so I created an issue. Anyone can do this to file improvements as well as bugs for members of the team to code.
Trace Flags
The previous release of dbachecks brought 2 new checks for traceflags. One for traceflags expected to be running and one for traceflags not expected to be running.
You will need to have installed dbachecks from the PowerShell Gallery to do this. This can be done using
Install-Module -Name dbachecks
Once dbachecks is installed you can find the checks using
Get-DBcCheck
you can filter using the pattern parameter
Get-DBcCheck -Pattern traceflag
This will show you
- the UniqueTag which will enable you to run only that check if you wish
- AllTags which shows which tags will include that check
- Config will show you which configuration items can be set for this check
The trace flag checks require the app.sqlinstance configuration which is the list of SQL instances that the checks will run against. You can also specify the instances as a parameter for Invoke-DbCheck as well.
The configuration for the expected traceflags is policy.traceflags.expected By default it is set to null. You can see what configuration it has using
Get-DBcConfig policy.traceflags.expected
So if you want to check that there are no trace flags running, then you can run
$instance = ‘sql0’ Set-DbcConfig -Name app.sqlinstance -Value $instance Invoke-DbcCheck -Check TraceFlagsExpected
Maybe this instance is required to have trace flag 1117 enabled so that all files in a file group grow equally, you can set the trace flag you expect to be running using
Set-DbcConfig -Name policy.traceflags.expected -Value 1117
Now you when you run the check it fails
Invoke-DbcCheck -Check TraceFlagsExpecte
and gives you the error message
[-] Expected Trace Flags 1117 exist on sql0 593ms Expected 1117 to be found in collection @(), because We expect that Trace Flag 1117 will be set on sql0, but it was not found.
So we have a failing test. We need to fix that. We can use dbatools
Enable-DbaTraceFlag -SqlInstance $instance -TraceFlag 1117
This time when we run the check
Invoke-DbcCheck -Check TraceFlagsExpected
it passes
If you just need to see what trace flags are enabled you can use
Get-DbaTraceFlag -SqlInstance $instance
Reset the configuration for the expected trace flag to an empty array and then set the configuration for traceflags we do not expect to be running to 1117
| |
and then run the trace flags not expected to be running check with
Invoke-DbcCheck -Check TraceFlagsNotExpected
It will fail as 1117 is still running
and give the message
[-] Expected Trace Flags 1117 to not exist on sql0 321ms Expected 1117 to not be found in collection 1117, because We expect that Trace Flag 1117 will not be set on sql0, but it was found.
So to resolve this failing check we need to disable the trace flag and we can do that with dbatools using
Disable-DbaTraceFlag -SqlInstance $instance -TraceFlag 1117
and now when we run the check
Invoke-DbcCheck -Check TraceFlagsNotExpected
it passes
The checks also work with multiple traceflags so you can set multiple values for trace flags that are not expexted to be running
Set-DbcConfig -Name policy.traceflags.notexpected -Value 1117, 1118
and as we saw earlier, you can run both trace flag checks using
Invoke-DbcCheck -Check TraceFlag
You can use this or any of the 95 available checks to validate that your SQL instances, singular or your whole estate are as you expect them to be.