Learn more about SQL Server tools

 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2000 vs SQL Server 2005 Startup Parameters


By:   |   Read Comments   |   Related Tips: More > SQL Server Configurations

Problem

SQL Server offers the ability to use startup parameters when your SQL Server instance starts. The default items include:

  • location of  master database data file (-d)
  • location of  master database log file (-l)
  • location of  SQL server error logs (-e)

In addition, you can set startup parameters for trace flags, starting your instance in single user mode, etc...here is a link to startup options for SQL Server 2005 which is pretty similar to options for SQL Server 2000.

With SQL Server 2000 the startup parameters are available right from Enterprise Manager, but not so with SQL Server 2005.  So how do you set the startup parameters for SQL Server 2005?

Solution

With SQL Sever 2000 access to the startup parameters is available right within Enterprise Manager.  You can get to the startup parameters by right clicking on the server name and selecting "Properties".  The following screen appears and there is a great big button "Startup Parameters..." which you can click and it will allow to see and set the startup parameters.

sql server properties general 2000

After clicking on "Startup Parameters" you get the following window.  From here you can click on the parameter and click "Remove" to remove it or click on "Add" to add a new parameter. It is pretty straight forward.

sql server startup parameters 2000

 

With SQL Server 2005 a lot of things have been moved to other applications and are not all available through SQL Server Management Studio.  One of these things is the startup parameters.  To find the startup parameters you need to go to the SQL Server Configuration Manager tool.  This can be found in the menus through: Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager.

Once the application is launched you will see the following screen.

sql server services 2005

  • select the SQL Server service that you want to set the startup parameters for such as "SQL Server (TEST1)" for our example
  • right click and select "Properties" and the following window will pop up
  • go to the "Advanced" tab

sql server startup parameters 2005

  • select "Startup Properties" and the following window will pop up

sql server startup parameters list 2005

  • from here you can add, change or remove your startup properties
  • note: each startup parameter is separated by a semi colon (;)
  • once you are done click "Apply" to save your changes
Next Steps
  • when you make the move to SQL 2005, now you know where to find the startup parameters
  • get familiar with setting startup parameters, these come in handy for setting trace flags and if you need to set the instance in single user mode


Last Update:






About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources


 









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    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools