Home Set SQL Server 'Maximum server memory in (MB)' using Powershell
Reply: 2

Set SQL Server 'Maximum server memory in (MB)' using Powershell

M0rty
1#
M0rty Published in 2017-11-13 20:39:19Z

I'm currently automating a VM and using a Powershell script to install SQL Server 2017.

I need to be able to script the Maximum server memory as well. Is this possible through the current configuration settings or will I need to create a custom script? If a custom script is required what would I need to do.

lad2025
2#
lad2025 Reply to 2017-11-14 08:02:42Z

You could use SQL (run from Powershell):

sp_configure 'show advanced options', 1;  
RECONFIGURE;  
sp_configure 'max server memory', 4096;  
RECONFIGURE;  

or use PowerShell cmdlet from dbatools.io:

Set-DbaMaxMemory

explicitly set the max memory to 2048 MB on just one server, “sqlserver1”

Set-DbaMaxMemory -SqlServer sqlserver1 -MaxMb 2048 
Andrei Odegov
3#
Andrei Odegov Reply to 2017-11-14 19:16:01Z

By using sqlps module it's possible to address this task the next way:

Import-Module Sqlps
$sql16 = ls 'SQLSERVER:\SQL\(local)'|? InstanceName -eq 'SQL16'
$sql16.Configuration.MaxServerMemory.RunValue
$sql16.Configuration.MaxServerMemory.ConfigValue = [Math]::Floor($sql16.Configuration.MaxServerMemory.RunValue * 1.5)
$sql16.Alter()
$sql16.Configuration.MaxServerMemory.RunValue
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.334658 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO