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


Set-DbaMaxMemory

This command retrieves the SQL Server ‘Max Server Memory’ configuration setting as well as the total physical installed on the server.

Screenshots

Examples

To set the max memory to the recommended MB on just one server named “sql2014”

Set-DbaMaxMemory -SqlServer sql2014

To explicitly set the max memory to 2048 MB on just one server, “sqlserver1”

Set-DbaMaxMemory -SqlServer sqlserver1 -MaxMb 2048

To find all servers in SQL Server Central Management server that have Max SQL memory set to higher than the total memory of the server (think 2147483647), then pipe those to Set-DbaMaxMemory and use the default recommendation.

Get-SqlRegisteredServerName -SqlServer sqlserver| Test-DbaMaxMemory | Where-Object { $.SqlMaxMB -gt $.TotalMB } | Set-DbaMaxMemory

From PowerShell, execute   Get-Help Set-DbaMaxMemory -Detailed   for more information on this function. Want to see the source code? See it on GitHub