5 Different Ways to Start SQL Server Services

By: Tim DelChiaro

Abstract: Here are few ways we penned down here which are used for standalone instance of SQL Server

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: Hide image
Common SQL Server Mistakes Webinar

  1. Object Explorer in SSMS (SQL server Management Studio)
  2. Windows Services.
  3. Net Start Command.
  4. Using executable Sqlservr.exe
  5. SSCM - SQL Server Configuration Manager.

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.

    Object Explorer in SSMS (SQL server Management Studio)

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.

Hide image

Caution: If we stop the service and close SSMS, then we can’t connect to start it.

    Windows Services

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:

Hide image
Click to see full-sized image

If we wish to add some startup parameter, we can do that by going to properties and click on start.

    The Net Start Command

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. Hide image

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.

    Using Sqlservr.exe executable.

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.

Hide image
Click to see full-sized image

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

    SQL Server Configuration Manager

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.

     Hide image

This tool can’t be used to control remote SQL Services.

    Conclusion

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.

Hide image

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 http://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.

   

Click to learn more about Embarcadero database tools related to this post and more from Pinal Dave:

Server Response from: ETNASC04