dbatools is a free PowerShell module with over 180 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

Get-Help

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

Source Code

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

Related commands