HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to specify SQL startup parameters in quiet installation via command line

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
linespecifysqlinstallationquietviahowparametersstartupcommand

Problem

I want to fix the identity gap bug in SQL Server 2012 via the -T272 startup parameter.

How can I automate this after installation? I found no install parameter for startup parameters.

Solution

If you go just over half way down on this link you will see the startup parameter for trace flags.

You can add them in the configuration manager here:

In this case it would be -T272 with the capital T. Per the link the lower case t is for specific flags used by the support engineers.

EDIT @MaxVernon pointed out I'd missed the point of the question :)
Shawn Melton has a post here were he discusses what you are trying to do. He has a script that you can run to add startup parameters. Mike Fal then wrote a followup here where he shares a function he wrote called Set-SQLStartupParameters that is supposed to be somewhat safer than Shawn's method.

Here is Shawn's code: (Mike's requires a download from GitHub so you can follow the links if you'd like that one.)

$server = 'MyServer'
$sqlservice = "MSSQLSERVER"
$sqlagentservice = "SQLSERVERAGENT"
$flagsToAdd = ';-T1117;-T1118;-T3226"

Add-Type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement,Version=11.0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"
$sqlwmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $server
$wmisvc = $sqlwmi.Services | where {$_.name -eq $sqlservice}
$wmisvc.StartupParameters = $wmisvc.StartupParameters + $flagsToAdd
$wmisvc.Alter()

$wmisvc.Stop()
Start-Sleep -seconds 15
$wmisvc.Start()

$wmiAgent = $sqlwmi.Services | where {$_.name -eq $sqlagentservice}
$wmiAgent.Start()

Code Snippets

$server = 'MyServer'
$sqlservice = "MSSQLSERVER"
$sqlagentservice = "SQLSERVERAGENT"
$flagsToAdd = ';-T1117;-T1118;-T3226"

Add-Type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement,Version=11.0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"
$sqlwmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $server
$wmisvc = $sqlwmi.Services | where {$_.name -eq $sqlservice}
$wmisvc.StartupParameters = $wmisvc.StartupParameters + $flagsToAdd
$wmisvc.Alter()

$wmisvc.Stop()
Start-Sleep -seconds 15
$wmisvc.Start()

$wmiAgent = $sqlwmi.Services | where {$_.name -eq $sqlagentservice}
$wmiAgent.Start()

Context

StackExchange Database Administrators Q#124448, answer score: 4

Revisions (0)

No revisions yet.