How to Start SQL Server Services

If nothing works, then a restart works

There are multiple ways to restart Sql server based on situations, Below are suggested for standalone instance of SQL Server only.

These are our Top 5 most commonly used methods:

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.

1. 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.

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

2. WINDOWS SERVICES

This is one of the most common way used by all DBAs. Top open services.msc, we can either go to start and (windows+R) 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.

3. 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. 

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.

4. 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.

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

5. 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.