#PSTip Change SQL Server MaxServerMemory configuration using SMO

Note: This tip requires PowerShell 2.0 or above.

As database administrators, we might want to configure SQL MaxServerMemory setting to ensure the SQL service does not occupy all available physical memory. This setting can be changed using SMO and PowerShell.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:ComputerName
$server.Configuration.MaxServerMemory.ConfigValue = 16384
$server.Configuration.Alter()

Make a note that the value of MaxServerMemory is in megabytes (MB).