Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Dukagjin Maloku - Dugi'DBA

Everything that I can post for the SQL Server ...

Stop'n'Start SQL Services via command prompt (cmd)

Using the command prompt for stopping and starting services is a very nice possibility and very fast! We know that SQL Server services must never stop, especially the SQL Server core engine service. During the system maintenance sometimes we need to restart the processes and it can be done also with cmd, but at the first we must stop them then start them again, and the restart process will happened! The correct way how to do it with cmd is:

NET STOP <ServiceName>
NET START <ServiceName>

Usually, the name of the service can be the default name of the service like MSSQLServer or can be a named service like MSSQL$InstancedName. However if you want to know the exact what you are trying to do, you can check the service name from regedit @ this location:

HKEY_LOCAL_MACHINE - SYSTEM - CurrentControlSet - services - MSSQLServer

The list of the available SQL Server services after successful install of the SQL Server with all services are:

  • Core engine  = MSSQLSERVER (default instance) or MSSQL$InstanceName (Instance Name)
  • SQL Agent = SQLSERVERAGENT (default instance) or SQLAGENT$InstanceName (Instance Name)
  • SSRS = ReportServer
  • SSIS = MsDtsServer100 (SQL Server 2008), MsDtsServer (SQL Server 2005)
  • SSAS = MSSQLServerOLAPService
  • SQL Browser = SQLBROWSER
  • Full-Text = MSSQLFDLauncher
  • SQL Server VSS Writer = SQLWRITER


An example:

Restarting the core engine of the SQL Server:

NET STOP MSSQLAGENT
NET STOP MSSQLSERVER

then

NET START MSSQLSERVER
NET START MSSQLAGENT

In the given example above, I have stop the SQL Agent service name first, why!? - the reason is that the SQL Server Agent Service si connected with core SQL Server Service. You can see, if you restart the SQL Server while the SQL Agent Server is running you will prompted to restart also the SQL Agent Service! So, in this case you should stop the SQL Agent first then SQL Server, then starting them again!

SQL Express Edition:

In SQL Server Express we have the same situation like Named Instance of the SQL Server, so if you want to restart the service you must do like this:

NET STOP MSSQL$SQLEXPRESS
NET START MSSQL$SQLEXPRESS

 ~~~ Stay Tuned!

Comments

Posted by Steve Jones on 24 June 2010

Good tip, and one I've used quite often when troubleshooting. This is also a quick and easy way to start in single user mode if you're recovering master.

Posted by Dukagjin Maloku on 24 June 2010

Steve, thanks for the additional info based on your experience!

Posted by lake_xp on 26 June 2010

Good tip, I also use the often forgotten SC command to start and stop services (sc stop "MSSQLSERVER") as well as to change service passwords (as per our Audit dept passwd change requirements) like so (sc config "MSSQLSERVER" obj= "DomainA\Kjaro" password= S0m3+h!ngC0mpl3x).  We create a batch file and run it on all servers with services requiring the passwd change. Before using the sc command or now Windows 2008 R2's service accounts, this was a really painful task

Posted by Dukagjin Maloku on 26 June 2010

@lake_sp

Thanks for your comment, also thanks for the nice info and for the sharing piece of your experience that is related to this post!

Posted by bkirk on 27 June 2010

Good tip. Thanks. Any recommendations on how to automate this for checking the services and re-starting them when they go down?

Posted by Hugo Shebbeare on 27 June 2010

Bkirk: you can set auto restart for the specific service properties, third tab - even reboot server is an option.

Great command-line reminder post Dukagjin.

Posted by Dukagjin Maloku on 28 June 2010

@bkirk - you can use the Hugo's option for the automate the procedure.

Posted by Dukagjin Maloku on 28 June 2010

@Hugo Shebbeare - thanks!

Posted by prashant.macs on 29 September 2010

Can someone explain why we need to restart sql services oftenly ?

Leave a Comment

Please register or log in to leave a comment.