SQL Server Articles, Tips, FAQs, Reviews, Whitepaper, News etc - MyTechMantra.com

SQL Server Database Engine Service Startup Options

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 (-).

Trending SQL Server Disaster Recovery Tips

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.

Reference

Database Engine Service Startup Options

Ashish Mehta

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Add comment

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips

Manning