Setting the Max Memory in SQL Server

See the source image

Here is a quick post about setting the max memory in SQL Server so that you got no worries if you forgot to set this at installation.

I get it we all been there before. This was a step I over looked when starting out with SharePoint many years ago. However I did recently have this come up in a discussion and I remembered and thought I should do this in PowerShell to exercise my scripting technique. It seems to be best used as an expression.


Test this in a dev environment first and not production to ensure compatibility with versions and the .net framework:

The Nitty Gritty:

Function Set-SQLServerMemory

    write-verbose "Begining"
     #$report+=New-Object -TypeName psobject -Property @{Servername="testServer"; TotalMemory=8076;} | select Servername,TotalMemory


    write-verbose "Begining the Process"

    foreach($ServerName in $ServerNames){
    Write-Verbose "In The Loop"
            $Rawmemorytotal = Get-wmiobject -ComputerName $ServerName -class Win32_PhysicalMemory | Measure-Object -Property Capacity -Sum
            $report+=New-Object -TypeName psobject -Property @{ 
                                                                TotalMemory=($Rawmemorytotal.Sum / 1MB)
            $min_os_memory = 2048 
            if( $TotalMemory -le $min_os_memory) {
                Write-Host "Memory too Low, Server could be Serverly impacted." -ForegroundColor Red 
            if ($TotalMemory -ge 8192) {
                $sql_mem = $TotalMemory - $min_os_memory
            } else {
                $sql_mem = $TotalMemory * 0.79 ;

    if ($MinMemory -eq 0) {
            $MinMemory = $MaxMemory
        [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
        $SQLServer = New-Object Microsoft.SQLServer.Management.Smo.Server($SQLInstanceName)
        if ($SQLServer.status) {
              $SQLServer.Configuration.MaxServerMemory.ConfigValue = $MaxMemory
            $SQLServer.Configuration.MinServerMemory.ConfigValue = $MinMemory   
            }#end if

        }#end Foreach

        return $report | select Servername,TotalMemory
        #return [int]$sql_mem ;  




Learning to access data from APIs (Invoke-WebRequest)

PowerShell is known for its wide capabilities to “shell” out changes and reporting data.

Websites are embracing the movement with data standards native to PowerShell and it’s cyphering functionality. Formats like JSON, and XML that are leveraged as responses for sites that load data.

So using invoke-webrequest and piping the results to convertfrom-json can turn this output in to readable in-memory native PowerShell styled .NET objects.

Using foreach-object and leveraging PowerShell 7 newest parallel feature could add some more speed to your larger task sets. I encourage you to explore these parameters.

 Check out the link for syntax





Scripting in other languges

Recently I noticed that there are not a lot of scriptwriters stick to PowerShell only.

There is a way to harness PowerShell to do other jobs like:

  • modify/build databases
  • Build/update websites
  • build and update dashboards
  • reconfigure Switches

I will be going over some simple examples that you can explore and build on.

Stay Tuned….


Effective practices

I have been scripting for quite sometime and sometimes there are older CMD tools that just work.

Most would say why reinvent the wheel?

Well if the wheel wasn’t constantly being revised and upgraded then all the newer methods and functions wouldn’t be as robust.

With programming in c# there are methods and you can build functions there and in powershell to simply your daily task and pc deployments.

Don’t forget that most great ideas come from someone thinking it could be done better. Making things better would never happen if we don’t take steps to refine our processes we have grown so attached to.

Do you have a function that you created to make your life easier? Please share in the comments below.