Ashok Kumar SQL Development and DBA Adminstrations Techi Blog
(SQL Materials for Every one to Share)
Counter of Festivals
Monday, 10 October 2016
What is Startup Parameters in SQL Server?
Working on a server is always something DBAs cherish. With every environment that they monitor, they want to know how applications can be optimally run on a specific server. In this constant pursuit of performance tuning, they always find unique ways of optimizing SQL Server startup. This article is around finding these parameters that most DBAs use in their daily life.
Whenever SQL Server starts, it needs three startup parameters:
This registry key depends on SQL Version and Instance Name (above the value of MSSQL12 is for SQL 2014 and SQL2014 is name of SQL Server Instance). There are situations where some additional parameters are needed. Few parameters are performance related, few are troubleshooting related. These parameters must be known to a DBA.
The right place to change startup parameter is via the SQL Server Configuration Manager. The interface to change the startup parameters has been changed in SQL Server 2012, so we are showing both these interfaces next.
Below is the Configuration Manager UI from SQL Server 2005 to SQL Server 2008 R2. We need to go to properties of SQL Server Service, go to “Advanced” tab and then click on dropdown at “Startup Parameters”. In this UI new parameters are separated by a semicolon. For example, if we want to add trace flag 1222 then we need to add “;-T1222” at the end.
As you can see the above interface is error prone and not very intuitive. This was the very reason why Startup parameters has been moved as a separate tab from SQL Server 2012.
This UI makes it easy to add/remove startup parameters. If there is a need to add a startup parameter temporarily, then that can be added while starting SQL Services via the command line. In the below examples, we would use this trick.
PARAMETER 1: -M
This is the parameter which is used to start SQL Server in single user mode. This option is generally used to restore master database. Here is an attempt to start SQL Server via “/m” parameter from command line:
2014-11-28 16:54:25.75 spid8sSQL Server started in single-user mode. This an informational message only. No user action is required.
PARAMETER 2: -F
This parameter is used to start SQL Server in “minimal configuration” mode. This parameter is used in situations when a DBA changed the configuration options causing SQL Server service startup failure. This startup parameter could be the only way to correct the mistakes in the SQL Server configuration.
2014-11-28 17:22:23.59 ServerCommand Line Startup Parameters:
2014-11-28 17:22:23.81 ServerWarning: The server instance was started using minimal configuration startup option (-f). Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
2014-11-28 17:22:24.04 spid8sSQL Server started in single-user mode. This an informational message only. No user action is required.
PARAMETER 3: -T
This is one of the very common parameter should be known to almost every DBA. This is a parameter which is used to enable trace flags in SQL Server. There are various documented and undocumented trace flags which are used to change the behavior of SQL Server Engine. One of the most commonly used trace flags is 1222 which is to print deadlock graph in XML format in Errorlog. There are few trace flags like 3608 which are used to troubleshoot and fix startup related problem. MSDN has list of trace flags available in SQL Server.http://msdn.microsoft.com/en-us/library/ms188396.aspx.
PARAMETER 4: -X
This is another documented trace flag to disable capture of performance counters in SQL Server Engine.
Notice that parameter is lower case x. if we try with upper case X then we would get error
An invalid startup option 'X' was supplied, either from the registry or the command prompt. Correct or remove the option.
These are some of the most commonly used startup parameters that are worth a look as a DBA. Hope this post got you going in understanding some of these parameters. Using UI or via command prompt as shown above, all these techniques are powerful and quite handy if you are going to become an seasoned DBA.