dbatools is a free PowerShell module with over 300 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


The Max Memory series (Get-DbaMaxMemory, Set-DbaMaxMemory, Test-DbaMaxMemory) first started out as Get-SqlMaxMemory and Set-SqlMaxMemory. Since then, things change a bit. First, the prefix was changed from Sql to Dba to better align with the rest of the commands, and Test was used to test and propose recommendations instead of Get.

Test-DbaMaxMemory information relating to SQL Server Max Memory configuration settings. It was inspired by Jonathan Kehayias’s post about SQL Server Max memory, and displays a SQL Server’s: total memory, currently configured SQL max memory, and the calculated recommendation.

Jonathan notes that the formula used provides a general recommendation that doesn’t account for everything that may be going on in your specific environment.



Calculate the ‘Max Server Memory’ settings for all servers within the SQL Server Central Management Server “sqlcluster”

Test-DbaMaxMemory -SqlServer sqlcluster,sqlserver2012

To find all servers in CMS that have Max SQL memory set to higher than the total memory of the server (think 2147483647) and set it to recommended value.

Test-DbaMaxMemory -SqlServer sqlcluster | Where-Object { $_.SqlMaxMB -gt $_.TotalMB } | Set-DbaMaxMemory


From PowerShell, execute Get-Help Test-DbaMaxMemory -Detailed for more information on this function.

Source Code

Want to see the source code? View Test-DbaMaxMemory.ps1 on GitHub

Related commands