With SQL Server Startup Parameters, the location of certain files in the startup phase of the SQL Server service, and some conditions in the instance level are determined.
In some cases, you should set the relavent startup parameter for detect and solve the problem.
During SQL Server Installation, sql server writes default startup parameters to windows registry.
To see the Startup parameters, in SQL Server Configuration Manager, on the SQL Server Services tab, right-click on the appropriate service as follows and click properties.
A screen appears as follows.
As you can see on this screen, -d, -e and -l startup parameters are available by default.
If you click “Add” after typing -f in the text field in “Specify a startup parameter”, the system will now run according to the values of this parameter each time it is turned on.
Sometimes, in order to solve problems at critical moments, you should set some parameters.
Dont forget:You should remove these parameters when the problem is solved.
When you press OK after adding the parameter by pressing Add, it gives a warning as follows that this parameter will be activated at the next service restart.
Any changes made will be saved; however, they will not take effect until the service is stopped an restarted.
I’m sharing some startup parameters descriptions below.
| Default Startup Parameters | Description |
| -d | Contains the information of the path of the master database’s mdf file. |
| -e | Contains the information of the path of ERRORLOG. |
| -l | Contains the information of the path of the master database’s ldf file. |
| Other Startup Parameters | |
| -c | This parameter reduces the start time of the service when SQL Server starts from the cmd command line.
Normally, SQL Server runs as Service when it starts. By setting this startup parameter, SQL Server doesnt runs as Service when it starts. So the start time is getting shorter. |
| -f | With this parameter, SQL Server works with a minimum configuration.
For example, if a server is unable to start because of a lack of memory in an over-commiting server, you can start it using this parameter. Or you can resolve the problem by using this parameter if the sql server does not open because it cannot find the disk to create tempdb. You can find the details in my article “SQL Server Service Is Not Starting Due to Tempdb“. |
| -m | The SQL Server service opens in single user mode.
If you do not want anyone else to connect, you can start the sql server with this parameter. With the -f parameter, we said that it was opened in single user mode. The difference between the two parameters is: with the -f parameter, the sql server starts with the minimum configuration. Caution: If you start SQL Server with the -m parameter, the users in the local admin group on the server will have the sysadmin right when connected to the sql server even if they do not have privileges on the sql server. |
| -g | Memory usage in “extended procedure”, “.dll file upload operations”, “distributed query”, and “automation object” uses memory other than memory pool used by sql server.
With this parameter, the amount of this allocated memory in MB is determined. This field is 256 MB by default. You should not set this parameter unless you see one of the following in SQL Server Error Log. “Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE <size>” “Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT <size>” |
| -mClient | Specifies the connection limit for the specified application. You can use it as follows.
-m”Microsoft SQL Server Management Studio – Query” -m”SQLCMD” Instead of setting this parameter, you should contact the application developers to solve the connection problems in the applications. Microsoft recommends that this parameter not be set as a security solution. |
| -n | Windows Application Log is not used to log SQL Server events.
Microsoft specifies that, if necessary this parameter should be used with the -e parameter, otherwise sql server events will not be logged. I didnt set this parameter any time. |
| -T | Enables SQL Server to start with the effects of the specified trace flag after “-T”. |
| -x | Sql server starts so that some tracing features are passive. |
| -E | Increases the number of allocated extents for each file in Filegroup. It should not be used in OLTP systems.
Can be useful in data warehouse systems with few users. |
| -h | With Hot Add Memory, the memory can be physically added without restarting the server.
To perform this operation, your operating system must be at least Windows Server 2003, SQL Server must be at least 2005. At the same time, SQL Server must be 64-bit or AWE enabled. You need to start SQL Server with the -h startup parameter. In SQL Server 2017, this parameter is no longer supported. |
