#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).

Filed in: Columns, Tips and Tricks Tags: , , ,
© 2018 PowerShell Magazine. All rights reserved. XHTML / CSS Valid.
Proudly designed by Theme Junkie.
%d bloggers like this: