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.
2) Right-click on the SQL Server service, select
Properties and select the
Advanced tab.
Type the desired values in the Startup Parameters box.
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