Dbatools Memory Usage Diagnostics

PowerShell Universal
1

If you are using dbatools and concerned about huge amount of memory usage, here’s some tips.

dbatools stores logs and error messages in an in-memory queue.

By default, this is enabled and will store up to 1024 log messages and 128 error messages. Because the log messages store so much information, you will see memory increase quickly. This is with 1024 messages in the queue.

image

If you have multiple dashboards running, long running scripts, and APIs using dbatools, each one will default to 1024 log messages and consume about this amount of memory per process.

As a test, I am running the script below every 10 seconds. It takes less than 10 minutes to reach this memory usage. I’m using the Integrated environment so that’s why the Universal.Server.exe process is using all the memory. You may see the memory usage in external PS processes.

This script prints the sizes of all the queues of messages. Finally, it invokes a simple query.

[Sqlcollaborative.Dbatools.Message.LogHost]::MessageLogEnabled = $true
[Sqlcollaborative.Dbatools.Message.LogHost]::ErrorLogEnabled = $true

$ErrorRecords = [Sqlcollaborative.Dbatools.Message.LogHost].GetField("ErrorRecords", [System.Reflection.BindingFlags]::Static -bor [System.Reflection.BindingFlags]::NonPublic) 
$ErrorRecords.GetValue($null).Count

$LogEntries = [Sqlcollaborative.Dbatools.Message.LogHost].GetField("LogEntries", [System.Reflection.BindingFlags]::Static -bor [System.Reflection.BindingFlags]::NonPublic) 
$LogEntries.GetValue($null).Count

[Sqlcollaborative.Dbatools.Message.LogHost]::OutQueueError.Count
[Sqlcollaborative.Dbatools.Message.LogHost]::OutQueueLog.Count

Invoke-DbaQuery -Database 'IronmanSoftware' -Query 'SELECT * FROM [IronmanSoftware].[dbo].[Orders]' -SqlInstance '(localdb)\MSSQLLocalDB' | Out-Null

The output looks like this. You can use this to see if your log queues are filling up.

Apr 28, 2022 3:00 PM  0 
Apr 28, 2022 3:00 PM  1024 
Apr 28, 2022 3:00 PM  0 
Apr 28, 2022 3:00 PM  21

You can disable dbatools logging like this.

Set-DbatoolsConfig -FullName logging.errorlogenabled -Value $false | Register-DbatoolsConfig
Set-DbatoolsConfig -FullName logging.errorlogfileenabled -Value $false | Register-DbatoolsConfig
Set-DbatoolsConfig -FullName logging.messagelogenabled -Value $false | Register-DbatoolsConfig
Set-DbatoolsConfig -FullName logging.messagelogfileenabled -Value $false | Register-DbatoolsConfig

You can also remove all log messages as well by clearing the log queue.

$LogEntries = [Sqlcollaborative.Dbatools.Message.LogHost].GetField("LogEntries", [System.Reflection.BindingFlags]::Static -bor [System.Reflection.BindingFlags]::NonPublic) 

$LogEntries.GetValue($null).Clear()

After adding that, my memory usage is much lower.

image

2 Likes
2

If running in IIS with a service account, is the Register-DbaToolsConfig only needed to be executed one-time as that user? Or would you add the logging disable commands to the process/dashboard startups?

3

It looks like you can set this at the system level using the -Scope parameter to avoid having to call it everywhere: dbatools/Register-DbatoolsConfig.ps1 at 90474c562c7696c8957c38e8becbb4ca408f53a8 · dataplat/dbatools · GitHub

4

Its so awesome to see, that you “support” 3rd party tools on an best effort basis! :slight_smile:

There seems to be an issue with the cmdlets, as described in this issue.
Register-DbatoolsConfig dont register in Registry · Issue #8368 · dataplat/dbatools (github.com)

Our PSU have been memory leaking like crazy lately, but after we manually defined the registry keys, it seems to have made huge difference.

5

I would like to try this, but am unable to find the root path of: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\WindowsPowerShell…

Any help?

Edit: It looks like DBATools is scoped to the user the way I installed it. I don’t actually have a path under the Local_MACHINE. Not sure if I need to install under HKEY_LOCAL user and add that path though to get PSU to use that key registry.

6

Looks like you found the solution yourself :slight_smile:
But you can run this command, to confirm if its pulling the value from Registry:

image
image853×92 7.52 KB

7

It’s definitely not holding the value of False for that config property when I go to restart my session. I am still not sure as to where I need to add the registry for DBATools under PSU to pick it up.

8

The best thing you can do, is then to upvote / watch the issue ive submitted i guess.
Register-DbatoolsConfig dont register in Registry · Issue #8368 · dataplat/dbatools (github.com)

Because it does look like an bug in the module :slight_smile:

9

Still no response from DBATools team =[

10

Bit of a zombie bump here…

But I’m not sure if if piping Set-DBAToolsConfig is suppose to work…

This, however is what worked for me.

Set-DbatoolsConfig -FullName logging.errorlogenabled -Value $false
Set-DbatoolsConfig -FullName logging.errorlogfileenabled -Value $false
Set-DbatoolsConfig -FullName logging.messagelogenabled -Value $false
Set-DbatoolsConfig -FullName logging.messagelogfileenabled -Value $false

Get-DbatoolsConfig -FullName Logging.* | Register-DbatoolsConfig -Scope SystemDefault

I found this after looking at their documentation ( dbatools docs | Register-DbatoolsConfig) which does not include any examples of piping “Set” to “Register”.

11

interesting… I had to go into the registry and add this myself, but perhaps this was my issue all along?

12

Better late than never - but the dbatools team came forward, and added that a -PassThru parameter is mandatory - Register-DbatoolsConfig dont register in Registry · Issue #8368 · dataplat/dbatools · GitHub

So the code should look like this instead:

Set-DbatoolsConfig -FullName logging.errorlogenabled -Value $false -PassThru | Register-DbatoolsConfig
Set-DbatoolsConfig -FullName logging.errorlogfileenabled -Value $false -PassThru | Register-DbatoolsConfig
Set-DbatoolsConfig -FullName logging.messagelogenabled -Value $false -PassThru | Register-DbatoolsConfig
Set-DbatoolsConfig -FullName logging.messagelogfileenabled -Value $false -PassThru | Register-DbatoolsConfig