SQL Server startup parameter in sql server 2005

SQL Server Configuration manager can help you to add or modify SQL Server Startup parameters in SQL Server 2005, This post will walk you through the steps involve in changing of startup parameters in SQL Server.

Using SQL Server Configuration Manager

To change the startup parameter options we have to connect to SQL Server Configuration Manger

  • Click on start – All Programs – Microsoft SQL Server 2005 – Configuration Tools –SQL Server Configuration Manager

Here you will see all running sql server services

  • Now Right click on SQL Server (MSSQLServer) services for default instance
  • Click on properties –  click on Advance option
  • Startup parameter (Here you will find the default location of the master data, log and error log files)

startup parameter in sql server

Default parameters

-d : master_data_file_path

-l : master_log_file_path

-e : error_log_file_path

  • Edit the location of the files with new location
  • Click on OK
  • stop the SQL Server services
  • Copy the files from default location to new locations
  • Start the SQL Server services

Optional Startup Parameter for SQL Server

-c : Starting SQL Server independent of the service control manage, Useful for troubleshooting the startup issues

-f : This parameter starts an instance of the sql server with the minimal configuration and can be enable through sp_configure update option.

-g : Amount of memory leave sql server for allocation of the sql server process

-m : To start the sql server services on single user mode.

-n : This option used to turn off features of logging the sql server errors in the windows application logs.

-s :  Used to start the named instance of sql server.

-T :  Used to start the trace flag for the troubleshooting issues.

-X : use to disable some features of sql servers like CPU time and cache Ratio statistics ,DBCC SQLPERF command, DMV, information related to performance counters.


Leave a Reply




8 − 6 =