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 SQL 2005 SQL 2008 SQL 2008 R2 SQL 2012
ConfigParam FULL Yes Yes Yes Yes
ConfigParam WORKSTATION Yes Yes Yes Yes
ConfigParam CLIENT N/A N/A N/A N/A

FineBuild also uses the following parameters to help configure the SQL Startup Parameters:
Prameter Default Description
TF1 T1222 Provides detailed information if a deadlock occurs
TF2 T3604 Sends output from advanced DBCC routines (e.g. DBCC PAGE) to the client running the command
TF3 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 T845 Allows SQL Server Standard Edition to exploit the Windows Lock Pages in Memory privilege, which improves performance
TF5 (blank) Available for use
TF6 (blank) Available for use
TF7 (blank) Available for use
TF8 (blank) Available for use
TF9 (blank) Available for use

The TF1 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

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 Feb 14, 2013 at 12:19 PM by EdVassie, version 5

Comments

No comments yet.