SQL Server Startup Parameters

When we install SQL Server, the setup writes default startup options like Master database location, error log location etc. to Windows Registry and the same will be used during startup of a SQL Server instance. We can add our own parameters using SQL Server Configuration Manager for troubleshooting purposes or for DBA activities.

Let’s open SQL Server Configuration Manager from Start Menu -> Configuration Tools:



Select properties of SQL Server instance and click on “Startup Parameters.” I  will configure my instance to start in single user mode by adding –m parameter as shown below:



After clicking OK, it will show the below warning saying “changes will reflect only after restarting the service”.



Click OK and restart service and try to connect to that instance using SSMS or SQLCMD. It will show the below message when a second user tries to connect to it.



To configure startup options, user should be part of the local administrators group.

Since, SQL Server Configuration Manager writes startup parameters to the registry. They take effect upon the restart of Database Engine.

We can query startup parameters from SSMS using below query:



It is a good practice to use Configuration Manager instead of Windows Service Control Manager (services.msc) to change any settings of an SQL Server instance\service.

I am ending things here, I hope this blog will be helpful for all.