3

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

Filed in: Columns, Tips and Tricks Tags: , , ,

3 Responses to "#PSTip Change SQL Server MaxServerMemory configuration using SMO"

  1. Sam says:

    Regarding setting the SQL server max memory :
    I tried doing this but it fails to change the configuvalue. I have added the SMO dll
    Add-Type -Path “C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\x64\Microsoft.SqlServer.Configuration.Smo.dll”

    $sStrSQLServer = new-object (‘Microsoft.SqlServer.Management.Smo.Server’) localhost
    $sStrSQLServer.Configuration.MaxServerMemory.ConfigValue = 4000
    $sStrSQLServer.Alter()

    So I tried below

    $sStrSQLServer = New-Object Microsoft.SqlServer.Management.Smo.Server localhost
    $sStrSQLServer.ConnectionContext.LoginSecure=$false;
    $sStrSQLServer.ConnectionContext.set_Login($sStrSAUserAccountName)
    $sStrSQLServer.ConnectionContext.set_Password($sStrSAUserPassword)

    try
    {
    $sStrSQLServer.ConnectionContext.Connect()
    $sStrSQLServer.Configuration.MaxServerMemory.ConfigValue = 4000
    $sStrSQLServer.Configuration.Alter()
    }

    But above fails with error as Exception setting “ConfigValue”:Failed to retrieve data for the request

    What am I missing above

Leave a Reply

Submit Comment

© 2016 PowerShell Magazine. All rights reserved. XHTML / CSS Valid.
Proudly designed by Theme Junkie.
%d bloggers like this: