MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter












































Latest SQL Server Tips

SQL Server: Different Startup Options for SQL Server Database Engine Service

Read Comments   |   Related Topics: SQL Server Disaster Recovery Tips > Next Topic: SQL Server Indexing Articles & Tips


Introduction

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.

Related Tips

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	
  • -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:


  • o Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE  <size>
    o 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.

Thank you for taking your time to read this article. Let's be Connected....

Sign-up for Our Newsletter to Get Free SQL Server Tips and News to Build your Career

Like MyTechMantra on Facebook to get updates on What's Happening in SQL Server

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:

More... Disaster Recovery Tips for DBAs and Developers







Last Updated On: May 03, 2016



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: