SQLPS.com

Ok. First post. Let’s see how this goes.

So they’re called ‘Startup Options’ or ‘Startup Parameters’ interchangeably. I have no idea why. In this post, I intend to show you how to change these programatically (with a script). The example is restricted to one of the many Startup Options that SQL Server accepts.

Here’s the scenario. You need to change the size of the MemToLeave area on a SQL instance . You read somewhere that you need to add/modify the ‘-g’ startup option. Fine, you go ahead and start SQL Server Configuration Manager, you right-click on the SQL service for the instance which you want to modify, right-click on it, select properties, click on the advanced tab, and look for ‘Startup Parameters’. Then, you edit the field, go to the end of the line and add ‘-g<some_value>’ – with <some_value> being any positive integer. I’ve usually seen DBAs increase it in chunks of 128Mb, but that’s just a rule of thumb. You restart your instance and voila — you’re done.

Well, that’s great — but what if you needed to change the size of the MTL on many servers?

Here is where Powershell, SMO and WMI come to the rescue.

Disclaimer: I handle many instances, and some of those have less than 4Gb of RAM. It was decided that only on servers reporting total RAM = 3.5Gb (considering that some servers suck up some RAM for graphics, etc) we would bump up the size of the MTL to 512Mb. The script reflects this criteria. Modify and use at your own risk.

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo.Wmi")
 
$serverList = Get-Content 'path-to-your-serverlist.txt'
 
foreach ($serverName in $serverList) {
	# TotalPhysicalMemory is in Kilobytes, so we need to do the math -- there are other ways to do it, I know.
	$ram = (Get-WmiObject -Class Win32_ComputerSystem -ComputerName $serverName).TotalPhysicalMemory / 1024 / 1024
 
	if ($ram -ge (3.5 * 1024)) {
		$wmiserver = New-Object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" $serverName
		$startupParameters = $wmiserver.Services['MSSQLSERVER'].StartupParameters # YMMV on the service name if you use named instances
		$mtlSetting = $startupParameters.split(';') | ? { $_.StartsWith('-g') }
 
		if ($mtlSetting -eq $null) {
			$wmiserver.Services['MSSQLSERVER'].StartupParameters += ';-g512'
		}
		else {
			$wmiserver.Services['MSSQLSERVER'].StartupParameters = $startupParameters.Replace($mtlSetting, '-g512')
		}
 
		$wmiserver.Services['MSSQLSERVER'].Alter()
		# Now reboot the box!
		$wmiOs = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $serverName
		$wmiOs.Reboot()
	}
}

Tagged: MemToLeave, Powershell, SMO, SQL Server, WMI