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

The SQL startup parameter configuration relates to Process Id 5AD and is controlled by the parameters below:

Install Parameter Build SQL2005 SQL2008 SQL2008R2 SQL2012 SQL2014
ConfigParam FULL Yes Yes Yes Yes Yes
ConfigParam WORKSTATION Yes Yes Yes Yes Yes
ConfigParam CLIENT N/A N/A N/A N/A N/A

FineBuild also uses the following parameters to help configure the SQL Startup Parameters:
Prameter SQL Version Default Description
TF1 All T1222 Provides detailed information if a deadlock occurs
TF2 All T3604 Sends output from advanced DBCC routines (e.g. DBCC PAGE) to the client running the command
TF3 All 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
TF4 All T845 Allows SQL Server Standard Edition to exploit the Windows Lock Pages in Memory privilege, which improves performance
TF5 All T3023 Force backups to use CHECKSUM parameter. More details are given in http://support.microsoft.com/kb/2656988/en-US
TF6 SQL2008R2 and above T2371 Improve Auto Statistics Update behaviour. More details are given in http://support.microsoft.com/kb/2754171
TF7 All (blank) Available for use
TF8 All (blank) Available for use
TF9 All (blank) Available for use

The TF7 to TF9 parameters can also 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;
-T1222
-T3604
-T1118
-T845
-T3023
-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 © 2012 - 2013 Edward Vassie. License and Acknowledgements
Previous Configure Service Account Names Top Configure SQL Service Recovery Next

Last edited Aug 8, 2013 at 4:30 PM by EdVassie, version 6

Comments

No comments yet.