Configure SQL Startup Parameters

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

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:

SQL Version	Parameter	FULL Build	WORKSTATION Build	CLIENT Build
SQL2019	/SetupParam:	Yes	Yes	N/A
SQL2017	/SetupParam:	Yes	Yes	N/A
SQL2016	/SetupParam:	Yes	Yes	N/A
SQL2014	/SetupParam:	Yes	Yes	N/A
SQL2012	/SetupParam:	Yes	Yes	N/A
SQL2008R2	/SetupParam:	Yes	Yes	N/A
SQL2008	/SetupParam:	Yes	Yes	N/A
SQL2005	/SetupParam:	Yes	Yes	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
/TF18:	SQL2016 and above	T2422	Enforce Resource Governor CPU Time limit
/TF19:	SQL2016 and above	T9567	Enables compression of Availability Group data transmission
/TF20:	SQL2016 and above	T460	Replace error message 8152 with 2628, giving useful details if data truncated
/TF21:	SQL2016 and above	T7745	Speeds SQL Shutdown by preventing Query Store flushing data to disk
/TF22:	SQL2016 and above	T7752	Speeds SQL Startup by allowing Query Store asynchronous data load

The /TF01: to /TF09: parameters are not used by SQL FineBuild and are available for use by the site DBA. 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

   

2. Right-click on the SQL Server service

   Select Properties then select the Advanced tab

   Type the desired values in the Startup Parameters box

   

   The required startup values for SQL2019 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;
   -T1222
   -T3604
   -T845
   -T3023
   -T4199
   -T2371
   -T2422
   -T9567
   -T460
   -T7745
   -T7752
   

   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 - 2021. License and Acknowledgements

Previous Configure Service Account Names	Top	Configure SQL Service Recovery Next