SQL Server: Different Startup Options for SQL Server Database Engine Service
Related Topics: SQL Server Disaster Recovery Tips>
Next Topic: SQL Server Indexing Articles & Tips
SQL Server Database Engine supports different startup options for SQL Server Database Engine Service. A database administrator can set the startup options very easily using SQL Server Configuration Manager. In this article we will explore startup options available in SQL Server Database Engine in detail.
Starting SQL Server 2012 the startup options have been improved. For more information, see Improved Startup options in SQL Server 2012 and Later Versions.
When to Use SQL Server Startup Options for Troubleshooting?
Database administrators should use some of the below mentioned startup options based on your need such as to start SQL Server in a Single User Mode or to Start SQL Server with Minimal Configuration. The easiest way to start SQL Server for troubleshooting will be to use the startup option -m or -f while manually starting sqlserve.exe
If you are starting SQL Server using net start, then use a slash (/) for startup options instead of a hyphen (-).
Different Startup Options Supported in SQL Server
- -d master_file_path startup option: This startup option is for master database file. If you wish to change the default location of master database data file then you would need to update the path here.
- -l master_log_path startup option: This startup option is for master database log file. If you wish to change the default location of master database log file then you would need to update the path here.
- -e error_log_path startup option: This startup option is for SQL Server Error Log file. If you wish to change the default location of SQL Server Error Log file then you would need to update the path here.
- -c startup option: Use this startup option in SQL Server if you want to shorten the startup time when starting SQL Server Database Engine service from the command prompt.
- -f startup option: Use this startup option in SQL Server if you want to start SQL Server Database Engine with minimal configuration. This is a very useful startup option which a DBA can use to troubleshoot issues with SQL Server Configuration such as over committing memory is preventing the server from starting. TempDB is not starting up etc. When you use this startup option the SQL Server will start in Single User Mode.
- -m startup option: Use this startup option in SQL Server to start an SQL Server Instance in Single User Mode. This startup option is used if you are experiencing problems with system databases and should be repaired. This enables SP_CONFIGURE 'Allow Updates Option' thereby allowing any member of server's local administrator group to connect to an instance of SQL Server as a member of System Administrator (SA) fixed server role. For more information see Steps to Connect to SQL Server When all System Administrators are Locked Out.
• Improved Startup options in SQL Server 2012 and Later Versions
• How to Identify Deadlocks in SQL Server Using Trace Flags
• How to Enable BACKUP CHECKSUM in SQL Server Using Trace Flags
• How to Repair a Suspect Database in SQL Server
• How to Disable SQL Server from writing every successfully backup entry in SQL Server Error Logs
• SQL Server Disaster Recovery Tips for DBAs and Developers
- -m "Client Application Name" startup option: Use this startup option to limit the connections to a specified client application. For example, use -m "SQLCMD" to limit the connection to a single connection and to connect through SSMS use -m "Microsoft SQL Server Management Studio - Query".
- -E startup option: Use this startup option if you want to increase the number of extents which are allocated for each file within a file group. However, this option is not supported in 32 bit release of SQL Server
- -s startup option: Use this startup option if you want to start a named instance of SQL Server. If you do not specify -s parameter set it will try to start the default instance of SQL Server. To start a named instance you must switch to appropriate BINN directory of the SQL Server Instance in Command Prompt and then start sqlservr.exe
Example: sqlservr.exe -s InstanceName
o Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE <size>
o Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT <size>
o SQL Server Performance Monitoring Counters
o Collecting Information for DBCC SQLPERF Commands
o Collecting information for some dynamic management views
o Keeping CPU Time
o Keeping Cache-Hit Ratio Statistics
Troubleshooting SQL Server Performance issues using Startup Options
There can be certain scenarios when you would like to use startup option in SQL Server. This can be while troubleshoot certain issues in SQL Server such as starting SQL Server with a trace flag to identify deadlocks, Disables lock escalation based on memory pressure, Disable the entry of every successful backup operation which gets added to SQL Server error log etc. When you use startup option the values are stored in registry key there by enabling SQL Server to start with these options every time it starts until the startup option is removed.
Thank you for taking your time to read
this article. Let's be Connected....
If you are using any of the above mentioned startup options in your environment then it is highly recommended to document the same so that you can configure them in case of any disaster.
More... Disaster Recovery Tips for DBAs and Developers
- Different Ways to Retrieve SQL Server Configuration Details
- How to Move TempDB Database Files to a New Drive in SQL Server
- Improved Startup Parameters in SQL Server 2012
- Steps to Connect to SQL Server When all System Administrators are Locked Out
- How to Repair Suspect Database in SQL Server
- How to Connect to a Named Instance of SQL Server
- Introduction to SQL Server Configuration Manager
- SQL Server Disaster Recovery Tips for DBAs and Developers
- How to Perform PARTIAL Backup in SQL Server a Step by Step Tutorial with Examples
Last Updated On: May 03, 2016
Please leave your Valuable Comment or Let us know how this article helped you: