In the SQL world, it is an important activity to perform SQL Server installation for a database administrator. Have you ever noticed ‘SQL Server Startup Parameters’ for the SQL Service? You might not have noticed them, but these parameters are beneficial for DBAs.
Let’s say you install the SQL service database engine. After installation, services are running in the automatic mode and you can connect to the SQL instance.
Let me start with a simple but logical question – What is the sequence for the SQL Service startup?
The high-level sequence of SQL Service start-up
It is a useful thing to know about the startup of SQL Services. The high-level steps are:
- Once we install SQL Server, we specify the credentials on which the service will run. Firstly, the credentials of the service account are verified. If the verification is successful, it starts the process to start SQL Service. In case of failure, we get an error message – The request failed, or the service did not respond in a timely fashionYou can go to the event viewer and check the logs for detailed information
- In the next step, it reads the SQL Server startup parameters from the registry (we will cover in detail in later part of this article) and verify the data file, log file location of the master database along with the error log path
- It allocates the memory and CPU to SQL Server as per the configuration
- It starts up the master database
- The master database contains an entry for all other system databases and user databases. It reads the information of the data file and log file of the databases and starts the recovery process (Analysis, Redo and Undo phases) for the databases
- It creates the tempdb database file and logs files as per the initial size, number of data files etc.
- It starts the default trace audit; startup extended event sessions and records all events in the SQL Server error log
- SQL Server attempts to register Service Principal Name ( SPN) to use Kerberos authentication
- It opens the SQL Server port and starts accepting a connection to the database. Once it is ready, you get a message in the SQL Server error logSQL Server is now ready for client connections. This is an informational message; no user action is required
SQL Server startup parameters
This article focuses on the startup parameters. To view the SQL Server startup parameters, right-click on the SQL Server Service and go to properties. It opens the SQL Services properties window.
Click on the Startup Parameters. You can see the default configured SQL Server startup parameters -d, -e and -l and their values.
Let’s look at the description of each default SQL Server startup parameter.
- -d: It is the primary data file path of the Master database
- -l: It is the transaction log file path of the Master database
- -e: it is the SQL Server error log path. It is essential to know the SQL Server error log file path to investigate any issues with SQL Server. Suppose you are not familiar with the SQL instance and if you do not know the path of the error log file, you need to go through each drive to check the file. You can open the SQL Server Configuration Manager and look at the error log location
We can specify a few other SQL Server startup parameters in SQL Server Configuration Manager.
-f (Minimal Configuration):
It starts SQL Server in a minimal configuration. Suppose we have a scenario in which SQL Service is not running due to over-committing memory. We can use this startup parameter and start SQL services in single-user mode troubleshoot further. We need to note the following in the minimal configuration startup of SQL Server.
- SQL Server remains in single-user mode
- It does not execute the CHECKPOINT process to flush the dirty pages
- It cannot run any startup stored procedures, triggers
Suppose after restarting the SQL Server, it does not start; in the error logs, you find out that the TempDB files could not be created because it is not pointing to the correct location. We cannot alter tempdb file paths as well because we are not connected to SQL Server. In this case, this parameter helps us to start SQL Server with minimal configuration. We can connect to SQL Server with this parameter with SQLCMD and execute an alter database command. Restart SQL Services, and you can connect to SQL Server.
Open an administrative command prompt. Go to the binn directory of SQL Server and run the following command.
>Sqlserver.exe -s SQL2019CTP /f
In this command, we specified named instance using -s parameter. If you connect with the default instance, we do not need to use this parameter.
It opens the SQL Server using minimal configuration. It shows SQL Server logs as well, and you can see an entry for it.
If the SQL Services are already running, you cannot start SQL Server in a minimal configuration. If you try to do, you get the following error messages.
Now you can open Sqlcmd and connect with the SQL Server to execute the query in minimal mode.
>sqlcmd -S kashish\sql2019ctp -E
You can press CTRL+C to move out from the minimal mode.
-m (Single user Mode):
It is a useful SQL Server startup parameter to start SQL Server in a single user mode. We might need to use a single-user mode in SQL Server to fix certain issues. For example, suppose you have completely locked out the SQL Server, and no one can connect to the SQL Server. In this case, you can use this mode to connect to SQL and reset the admin password or create a new user with the admin permissions.
Go to the SQL Server Configuration Manager, right-click the SQL Server Service, and choose Properties. Go to Startup Parameters as shown below, specify the -m parameter and click on Add.
Click on Apply, and you get a warning message to restart SQL Service to activate the SQL Server startup parameter. If we do not restart SQL Server, the changes will not be active.
Now start SQL Service and connect to SQL Server.
Only one user can connect to SQL Server in this mode; if you try to connect to more than one connection, you get the following error:
sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user ‘kashish\Test’. Reason: Server is in single-user mode. Only one administrator can connect at this time.
-Client Application Name:
In the single-user mode, we can limit the connections from a specific application as well. For example, we might want to limit the connection for SQLCMD command-line tool or SSMS.
- Specify -m”Microsoft SQL Server Management Studio – Query” SQL Server startup parameters for SSMS
- Specify -mSQLCMD for SQLCMD connection
We need to note that the client application name is a case sensitive string. We should not use for implementing security restrictions for all connections; we can use it with single-user mode only. You cannot use the SQL Server Configuration manager to set this startup parameter.
-n (Windows event logs):
You might be aware that SQL Server logs all critical events in the SQL Server error logs as well as Windows Logs. If we do not want to use windows events logs to be populated with the SQL error logs, start SQL Service with the –n parameter. We need to specify –e SQL Server startup parameter as well along with this else SQL Server error logs also do not get any entry.
-s (named instance):
In SQL Server, we can use a default instance and multiple named instances in the same server. If we want to start a named instance in SQL Server with the command line, we need to use –s SQL Server startup parameter along with the instance name. In the SQL Server error logs also, we get an entry for this parameter.
Suppose we have a named instance SQLDemo then in the error logs, you can see an entry
Command Line Startup Parameters: -s “SQLDemo.”
-x (Disable data collection):
Sometimes, we might require to start SQL Services and do not capture any performance monitor parameter values, data from the dynamic management views, we can start SQL Services with the –x parameter. It does not capture the data for the following things.
- Performance counter data for the SQL Server
- Dynamic management view statistics
- Certain extended events data
- It does not monitor CPU; Cache hit ratio data as well
- Note: We should be cautious with this parameter. It is not recommended, especially for the production SQL Server instance. You will not be able to get the troubleshooting data in case of any issue.
-E (Number of extents):
We can use this SQL Server startup parameter to increase the number of extents for each data file in a filegroup. This parameter might be useful for data warehouse scenarios. We should test the impact of this parameter with the application and the database before using it for SQL Service restart.
-k (Checkpoint speed):
We can use this parameter to set the CHECKPOINT speed in IO request per second. For example, -k100 specifies to use 100 MB per second checkpoint IO speed.
We should be careful in using this parameter as it might put a negative impact on the database backups, restore, recovery processes.
-T (Trace Flags):
In SQL Server, we use trace flags to change the system behavior or capture internal information. It is a useful SQL Server startup parameter and allows the SQL Server to start with the specified trace flag. Suppose we want to start SQL Service and it should start capturing any deadlock events as soon it gets user connections. We can add –T1222 trace flag in startup parameters to do this task.
- Note: We must use capital letter T along with the trace flag number. SQL Server also accepts small letter t, but these are internal trace flag (not available for DBA) and must be used in coordination with the Microsoft support engineers.
Conclusion
In this article, we explored SQL Server Startup Parameters for Database Engine Services. You should be aware of these parameters and use them as per the requirement.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023