If nothing works, then a restart works. This is the exact sentiments that an Administrator has when it comes to working with software. If you...
If nothing works, then a restart works. This is the exact sentiments that an Administrator has when it comes to working with software. If you are working as SQL Server DBA, you must have done this many times – restart SQL Server Services. It’s always interesting to know multiple ways to do the same thing because based on situations, we may need to use easy ones. Here are few ways we penned down here which are used for standalone instance of SQL Server. These are our Top 5 most commonly used methods:
In case of failover cluster instances we should be failover cluster manager to stop and start service by taking resource offline and online respectively.
Let’s see each one of them in detail.
The object explorer in SSMS can also start both local and remote servers as long as the remote server is connected and we have remote-service rights. Right Click and select the required action – Start, Pause, Resume, Stop. If we are not already connected, we can’t start a stopped instance.
Caution: If we stop the service and close SSMS, then we can’t connect to start it.
This is one of the most common way used by all DBAs. Top open services applet, we can either go to start > Run and type services.msc or by using the Windows Start, Programs, Administrative Tools, Services menu. Then, double-click (or right click) the MSSQLServer service, and right-click the required action. If we want to start a SQL Server named instance, look for the service called SQL Server (Instance Name). Let’s say our machine has named instance called SQL2014 then we would see SQL Server (SQL2014), as shown below:
If we wish to add some startup parameter, we can do that by going to properties and click on start.
Methods so far are the one to use User Interface, but we can also use the Net Start command. Type net start mssqlserver at the command prompt. To start a named instance, type net start MSSQL$instancename. In below command prompt, I have started default instance.
If we want to add additional startup parameters, we can append them in the command. For example, if we want to start SQL in single user mode then we can add /m as below
net start mssqlserver /m
If we want to use trace flag (let’s say Trace Flag 3608) then we can run below.
net start mssqlserver /T3608
We can also combine multiple startup parameter by appending them on same line.
We can also use sqlservr.exe to start SQL Server when we want to enable debugging or troubleshoot etc. To start an instance of SQL Server, we must run the sqlservr.exe program from the binn directory, where the SQL Server binary files are stored by default.
We have given -s parameter and given instance name to start particular instance. For default instance, we need to provide MSSQLSERVER. For complete list of startup parameters refer book online. http://technet.microsoft.com/en-US/library/ms190737.aspx
This is the best way to control the services because we are using the tool which is designed for this purpose. Start > Programs > Microsoft SQL Server (version) > Configuration Tools > SQL Server Configuration Manager. Once we click on SQL Server Services on left, we can see all services on right pane. Then we can right-click SQL Server (MSSQLServer), and then choose required action. A green icon next to the server name and indicates that the service is running.
This tool can’t be used to control remote SQL Services.
There are other ways like T-SQL and PowerShell but we have listed most commonly used and must know five ways. Each of these methods are useful at different places based on the situation in hand. Knowing these helps Administrators to use the best possible approach keeping his environment in mind.
Click to learn more about Embarcadero database tools related to this post and more from Pinal Dave:
| About Pinal Dave Technology Evangelist & Founder of SQL Authority Pinal Dave works as a Technology Evangelist (Database and BI) with Microsoft India. He has written over 2000 articles on the subject on his blog at https://blog.sqlauthority.com. During his career he has worked both in India and the US, mostly working with SQL Server Technology – right from version 6.5 to its latest form. Pinal has worked on many performance tuning and optimization projects for high transactional systems. He has been a regular speaker at many international events like TechEd, SQL PASS, MSDN, TechNet and countless user groups. |
Contact our sales team to get a personalized demo of our database management software for SQL Server!
