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

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)