Posted by: Colin Smith
DBA, memory, Powershell, SMO, SQL Server
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.