Limit the memory usage of SQL Server. We use it to set up developer PCs and other instances where a SQL Server should share the machine with other applications.

configure-sql-server.ps1

## Configure a SQL Server for running on a shared machine (set max memory)

Function Configure-SqlServer
{
    <#
        .Synopsis
            Configure a SQL Server for use with Panda.
        .Example
            Configure-SqlServer .\SQLExpress
            Configure the local SQL Server Express
    #>
    Param (
        [string] $server= ".\SQLEXPRESS",
        [string] $database = "master",
        [int] $maxMemoryInMegabytes = 4096
    )

    Set-Max-Memory $maxMemoryInMegabytes
}


Import-Module "sqlps" -DisableNameChecking



## Steps

function Invoke-SqlCommands 
{
    Param([string]$server, [string]$db, [string[]]$commands)
    Write-Host "Server:    $server"
    Write-Host "Database:  $db"
    foreach ($query in $commands)
    {
        Write-Debug $query
        Invoke-Sqlcmd -ServerInstance $server -Database $db -Query $query
    }
}

function Set-Max-Memory 
{
    Param([int]$maxMega)
    Write-Host "Configuring max server memory to $maxMega MB..."
    [string[]] $setMaxMemoryCommands = 
        "EXEC sys.sp_configure 'show advanced options', 1",
        "RECONFIGURE",
        "EXEC sys.sp_configure 'max server memory (MB)', $maxMega",
        "EXEC sys.sp_configure 'show advanced options', 0",
        "RECONFIGURE"
    Invoke-SqlCommands $server $database $setMaxMemoryCommands
}