functions/Set-SqlMaxMemory.ps1
|
Function Set-SqlMaxMemory { <# .SYNOPSIS Sets SQL Server max memory then displays information relating to SQL Server Max Memory configuration settings. Works on SQL Server 2000-2014. THIS CODE IS PROVIDED "AS IS", WITH NO WARRANTIES. Inspired by Jonathan Kehayias's post about SQL Server Max memory (http://bit.ly/sqlmemcalc), this uses a formula to determine the default optimum RAM to use, then sets the SQL max value to that number. Jonathan notes that the formula used provides a *general recommendation* that doesn't account for everything that may be going on in your specific environment. .PARAMETER SqlServer Allows you to specify a comma separated list of servers to query. .PARAMETER MaxMb Specifies the max megabytes .NOTES Author : Chrissy LeMaire (@cl), netnerds.net Requires: sysadmin access on SQL Servers .LINK https://dbatools.io/Set-SqlMaxMemory .EXAMPLE Set-SqlMaxMemory sqlserver1 Set max memory to the recommended MB on just one server, "sqlserver1" .EXAMPLE Set-SqlMaxMemory sqlserver1 2048 Explicitly max memory to 2048 MB on just one server, "sqlserver1" .EXAMPLE Get-SqlRegisteredServerName sqlserver| Get-SqlMaxMemory | Where-Object { $_.SqlMaxMB -gt $_.TotalMB } | Set-SqlMaxMemory Find all servers in CMS that have Max SQL memory set to higher than the total memory of the server (think 2147483647), then pipe those to Set-SqlMaxMemory and use the default recommendation. #> [CmdletBinding()] Param ( [parameter(Position = 0)] [Alias("ServerInstance", "SqlInstance", "SqlServers")] [string[]]$SqlServer, [parameter(Position = 1)] [int]$MaxMb, [Parameter(ValueFromPipeline = $True)] [object]$collection, [System.Management.Automation.PSCredential]$SqlCredential ) PROCESS { if ($SqlServer.length -eq 0 -and $collection -eq $null) { throw "You must specify a server list source using -SqlServer or you can pipe results from Get-SqlMaxMemory" } if ($MaxMB -eq 0) { $UseRecommended = $true } if ($collection -eq $null) { $collection = Get-SqlMaxMemory -SqlServer $SqlServer } $collection | Add-Member -NotePropertyName OldMaxValue -NotePropertyValue 0 foreach ($row in $collection) { Write-Verbose "Attempting to connect to $sqlserver" try { $server = Connect-SqlServer -SqlServer $row.server -SqlCredential $SqlCredential } catch { Write-Warning "Can't connect to $sqlserver or access denied. Skipping." continue } if (!(Test-SqlSa -SqlServer $server)) { Write-Error "Not a sysadmin on $servername. Skipping." $server.ConnectionContext.Disconnect() continue } $row.OldMaxValue = $row.SqlMaxMB try { if ($UseRecommended) { Write-Verbose "Changing $($row.server) SQL Server max from $($row.SqlMaxMB) to $($row.RecommendedMB) MB" $server.Configuration.MaxServerMemory.ConfigValue = $row.RecommendedMB $row.SqlMaxMB = $row.RecommendedMB } else { Write-Verbose "Changing $($row.server) SQL Server max from $($row.SqlMaxMB) to $MaxMB MB" $server.Configuration.MaxServerMemory.ConfigValue = $MaxMB $row.SqlMaxMB = $MaxMB } $server.Configuration.Alter() } catch { Write-Error "Could not modify Max Server Memory for $($row.server)" } $server.ConnectionContext.Disconnect() } return $collection | Select Server, TotalMB, OldMaxValue, @{ name = "CurrentMaxValue"; expression = { $_.SqlMaxMB } } } } |