TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
C# Corner
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
SQL Server Startup Parameters
Sateesh Arveti
Mar 09, 2016
10.9
k
0
2
facebook
twitter
linkedIn
Reddit
WhatsApp
Email
Bookmark
In this article you will learn 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.
SQL Server Startup Parameters
Next Recommended Reading
Optional parameters in SQL Server Stored Procedures