Previous Configure Service Account Names Manual Configuration Configure SQL Service Recovery Next

Configure SQL Startup Parameters

FineBuild can add standard Trace Flags to SQL Server parameters to improve the performance and manageability of SQL Server.

FineBuild SQL Startup Parameters configuration

Automated silent SQL startup parameter configuration relates to Process Id 5AE and is controlled by the parameters below:

Install Parameter Build SQL2005 SQL2008 SQL2008 R2 SQL2012 SQL2014 SQL2016 SQL2017
SetupParam FULL Yes Yes Yes Yes Yes Yes Yes
SetupParam WORKSTATION Yes Yes Yes Yes Yes Yes Yes
SetupParam CLIENT N/A N/A N/A N/A N/A N/A N/A

In order to maintain compatibility with older versions of SQL FineBuild, the parameter ConfigParam can also be used.

FineBuild also uses the following parameters to help configure the SQL Startup Parameters:

Parameter SQL Version Default Description
TF10 SQL2014 and below T1117 Improves space management by growing all files in a File Group at the same time
TF11 SQL2014 and below T1118 Improves space management, especially for tempdb. More details are given in KB328551, and the PSS Tempdb advice confirms it is useful in all versions of SQL Server
TF12 All T1222 Provides detailed information if a deadlock occurs
TF13 All T3604 Sends output from advanced DBCC routines (e.g. DBCC PAGE) to the client running the command
TF14 All T845 Allows SQL Server Standard Edition to exploit the Windows Lock Pages in Memory Right, which improves performance
TF15 All T3023 Force backups to use CHECKSUM parameter. More details are given in KB2656988
TF16 All T4199 Enable all Optimiser enhancements. More details are given in KB974006
TF17 SQL2008R2 and above T2371 Improve Auto Statistics Update behaviour. More details are given in KB2754171

The TF01 to TF09 parameters are not used by SQL FineBuild and are available for use. They can be used to supply a –g or other parameters if required.
Top

Manual SQL Startup Parameters configuration

The following steps show what you would have to do for manual SQL Startup Parameters configuration. FineBuild does all of this work for you automatically.

1) Start SQL Server Configuration Manager (Start -> Programs -> Microsoft SQL Server -> Configuration Tools -> SQL Server Configuration Manager)

Select SQL Server Services, then highlight the SQL Server service for the instance.
The default instance is called MSSQLSERVER.
Config Manager.png
2) Right-click on the SQL Server service, select Properties and select the Advanced tab.
Type the desired values in the Startup Parameters box.
Parameters.png
The required startup values are given below (The drive letter may change depending on site requirements).
-dE:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;
-eE:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\ERRORLOG.OUT;
-lE:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;
-T1117
-T1118
-T1222
-T3604
-T845
-T3023
-T4199
-T2371

Notes:
a. A permissions problem sometimes prevents some services being displayed. In this situation the startup parameters must be changed using Regedit (see FineBuild5ConfigureSQL.vbs for details).
b. The values for the –d and –l parameters are not changed
c. The –e parameter is changed, to alter the name of the error log file. The suffix .OUT is added to the ERRORLOG file as this causes the historical ERRORLOG files to also have the suffix .OUT, which makes it easier to edit these files using Notepad.
3) Click OK to save the values.

Copyright FineBuild Team © 2012 - 2017. License and Acknowledgements
Previous Configure Service Account Names Top Configure SQL Service Recovery Next

Last edited Jan 6 at 2:47 PM by EdVassie, version 10