The Multifunctioning DBA

Dec 7 2010   9:14PM GMT

Setting Min and Max Memory settings in SQL with Powershell

Colin Smith Colin Smith Profile: Colin Smith

So today I was thinking about some of the things that are currently not set up properly on some of my servers. I got to thinking that a lot of them were never set up properly for Min and Max Server memory. Most of them, that were installed before my time, are set up with the defaults and as you all know, that is not usually the best practice.

I thought it would be nice to have a way to progamatically set those values based on how much memory the server has total. I started tooling around in Powershell using SMO and I found the settings that I was looking for. It is really pretty simple to change theses values as I have tested it on my own local SQL Server. Here is what you need to do.

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “ServerName”

$s.Configuration.MinServerMemory.ConfigValue = 1024
$s.Configuration.MaxServerMemory.ConfigValue = 1024

The above code will set both the Min and Max Server memory settings to 1024 MB. This change will not be in affect until you restart sql server. But I now know that I can do this progamatically if I come up with a standard for these settings that is based on something that I can progamatically determine. My first thought is to do this based on the total Memory in the server and then figure out how to set both values. I will post more as I come up with it but I thought this was a nice little tidbit.

2  Comments on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • m60freeman
    You may also want to take into consideration how many instances may be running on one server. Two instances should get half the max memory value after your allowance for the OS and other processes, three instances should each get 1/3, etc. I have a development server with 6 instances running (I'd never do that in production!) so I have to be careful about this kind of thing.
    0 pointsBadges:
  • sam216
    Regarding setting the SQL server max memory :
    I tried doing this but it fails to change the configuvalue
    $sStrSQLServer = new-object ('Microsoft.SqlServer.Management.Smo.Server') localhost
    $sStrSQLServer.Configuration.MaxServerMemory.ConfigValue = 4000
    So I tried below 
    $sStrSQLServer =  New-Object Microsoft.SqlServer.Management.Smo.Server localhost
            $sStrSQLServer.Configuration.MaxServerMemory.ConfigValue = 4000
    But above fails with error as Exception setting "ConfigValue":Failed to retrieve data for the request
    What am I missing above
    10 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: