SQL SERVER – Start Services or Stop Services with PowerShell – Question to Readers?

SQL SERVER - Start Services or Stop Services with PowerShell - Question to Readers? system-shutdown Recently I received a very interesting question- I have no idea about its answer so I will request your help to answer this question.

Question:

“I am using a Powershell script to shutdown SQL Services. Here is the command I use.

Stop-Services 'mysqlserver' - Force

When I have to start the services, I use

Start-Services 'mysqlserver'

If you notice when I start this service, I do not have to use Force flag/command. If I do not use -Force flag when I try to stop services it gives me error that.

Stop-Service : Cannot stop service ‘SQL Server (MSSQLSERVER) (mssqlserver)’ because it has dependent services. It can only be stopped if the Force flag is set.

Now, here is the question to you – When I use -Force flag does it execute SHUTDOWN process internally or it does SHUTDOWN WITH NOWAIT?”

This is a very interesting question. I honestly have no answer to this question. From the syntax we can guess that it might be doing Shutdown with Nowait but again, guessing is not a good idea. In the past, there have been enough time I have been proven wrong when I have guessed anything. There is always a chance when what we see is not what happens internally.

Here is few additional information. When we execute SHUTDOWN command in T-SQL it waits for all the currently running Transact-SQL statements or stored procedures to finish. However, when we use syntax SHUTDOWN WITH NOWAIT it shuts down server immediately without even doing Checkpoint. This increases more work for the server when it restarts.

So here is the question again – Stop-Services ‘mysqlserver’ in powershell is equivalent to Shutdown or Shutdown With Nowait in T-SQL?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Powershell, SQL Server
Previous Post
Developers – Seven Foods Developers Love
Next Post
SQL SERVER – Start Services or Stop Services with PowerShell – Answer to Question

Related Posts

6 Comments. Leave new

  • Laerte Junior
    January 5, 2014 9:54 am

    My friend, excelent question (as always).Stop-Service performs a shutdown only. It Shuts down SQL Server performing checkpoints in every database. I am doing a blog post showing my test and will send to you :)

    Reply
  • Marc Jellinek
    January 6, 2014 12:26 am

    I don’t have time to test this, but wouldn’t it also make sense to use SQL Profiler to see what command is being sent to SQL Server (if any at all)?

    If the SHUTDOWN command (or SHUTDOWN WITH NOWAIT), it should be able to be captured.

    This is different than if Stop-Service is simply acting like a NET STOP command. I’m not sure how to capture the commands sent by NET STOP

    Reply
  • Neeraja Rao Kuppili
    January 7, 2014 11:58 pm

    Very information post and thanks Laerte for the test conducted and for your post too.

    Reply

Leave a Reply

Menu
Exit mobile version