SQL Server Startup Parameters

Sateesh Arveti
8y
11.1k
0
2

Blog

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:

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:

property

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

warning

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.

connect

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:

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.