Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Configuring Server Startup Parameters in SQL Server 2012


By:   |   Read Comments   |   Related Tips: 1 | 2 | 3 | 4 | More > SQL Server Configurations

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


Problem

While looking through new features and improvements in SQL Server 2012, an improvement I came across was how to configure SQL Server startup options. This is a very useful change which Microsoft has made, thereby making it easier for database administrators to change startup options. In this tip we will take a look at how easily you can change the SQL Server startup options in SQL Server 2012.

Solution

In previous versions of SQL Server, whenever a DBA had to change the SQL Server startup options he had to carefully add or modify the entries which were separated using semicolons (;). In the case where you missed adding the entries correctly, the SQL Server service will not start. In the below snippet you can see how this was done for SQL Server 2005 and SQL Server 2008 under the Advanced Tab in SQL Server Configuration Manager.

changing the start up options in sql server denali

In the new version of SQL Server, Microsoft has made configuring the SQL Server startup options an easy and simple process. Let's go through an example which demonstrates how one can configure SQL Server Startup Options.

1. In SQL Server Configuration Manager, click SQL Server Services on the left side pane.

2. In the right side pane, right click the SQL Server () Service and choose Properties from the drop down menu.

click sql server services

3. In the SQL Server Properties window navigate to the Startup Parameters tab as shown below.

  • To modify an existing parameter, select it from the "Existing parameters" list, make the change and click Update to save.
  • To add a new parameter, like a trace flag, type the new parameter and click on Add to save the change.
  • To remove an existing parameter select if from the "Existing parameters" list and click Remove.
in the sql server properties window navigate to the startup properties

4. Note that you still need to restart the SQL Server service for these changes to take effect just like older versions of SQL Server.

Next Steps


Last Update:


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools