“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
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)
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 :)
I am looking forward to your blog post. Once published I will update my blog post with link to your research.
Done my friend :)
Loved your post!!! Thank you, Laerte. I will write a follow up post tomorrow on this subject.
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
Very information post and thanks Laerte for the test conducted and for your post too.