#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=, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:ComputerName
$server.Configuration.MaxServerMemory.ConfigValue = 16384

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

About the author: Ravikanth C

Ravikanth is the founder and editor of the PowerShell Magazine. He is also a PowerShell MVP who works at Dell Inc. He blogs at http://www.ravichaganti.com/blog and you can follow him on Twitter @ravikanth.

Related Posts