Ashish MehtaSQL 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 (-).
Trending SQL Server Disaster Recovery Tips
- Database Backup Encryption in SQL Server 2014 a Step by Step Implementation Guide
- Different Ways to Enable Dedicated Administrator Connection in SQL Server
- How to Backup and Restore Resource Database in SQL Server
- How to Configure TempDB on Local Disk in SQL Server 2012/2014 Failover Cluster to Improve Performance
- How to Fix “BACKUP detected corruption in the database log” Error in SQL Server
- How to Identify the Location of Resource Database in SQL Server
- How to Repair Database in Suspect Mode in SQL Server
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. Read How to Increase Number of SQL Server Error Log Files, Limit SQL Server Error Log File Size in SQL Server, and How to Recycle SQL Server Error Log file without restarting SQL Server Service.
- -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.
- -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
- -g memory_to_reserve startup option: This startup option in SQL Server will leave available memory (MB) for memory allocations within the SQL Server process. The memory which is outside the memory pool is the are used by SQL Server for loading extended procedures (DLL), OLE DB Providers etc. The default is 256 MB. Use the default for the -g parameter unless you see any of the following warnings in the SQL Server error log:
- Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE <size>
- Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT <size>
- -n startup option: Use this startup option if you do not want SQL Server to record SQL Server Events in Windows Application Log. However, it is highly recommended to use -e startup option along with -n startup option otherwise none of the SQL Server events will be logged.
- -T trace# startup option: Use this startup option if you want to start an instance of SQL Server with a specified trace flag. DBAs can use the trace flags to start the server with non-standard behaviour.
- -x startup option: Use this startup option if you want to disable the below mentioned monitoring features.
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
How to configure SQL Server to Startup Using Certain Startup Options during Normal Operations
There can be certain scenarios when you would like to use startup option in SQL Server to 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.
Conclusion
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.
Chetna Bhalla
Add comment