Stop-Service is like the T-SQL Shutdown or Shutdown with Nowait ?


My good friend and SQL Server guru Pinal Dave did a excelent question in his site :

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

In this post, he ask if we stop the SQL Server service using stop-service –force it is the same to use T-SQL Shutdown or Shutdown with NoWait.

Lets see the documentation first.

The –force parameter in the stop-service is to use when the service has dependent services , as the SQL Server service has the SQL Server Agent .

image

 

Also , according the technet, other tools and methods to stop SQL Server performs checkpoint in each database :

image

But it not says nothing about PowerShell, so I had to test tracking the checkpoints  using trace flags 3502/3605 to log the checkpoints in the SQL Server Error log:

First Test : Using Shutdonw.

image

and it performs the checlpoints :

image

Second Test – Using NoWait

image

 

As we can see, no checkpoints  were performed

image

and the rollback were performed when started the SQL Server :

image

Third Test – Using Stop-Service

Firts enabled the trace flags :

image

Then run the stop-service :

 

image

and then :

image

So the asnwer is :

STOP-SERVICE -force  is like to use SHUTDOWN, performing the checkpoint in each database :)

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Algo que Esqueci de Categorizar. Bookmark the permalink.

3 Responses to Stop-Service is like the T-SQL Shutdown or Shutdown with Nowait ?

  1. Pinal Dave says:

    That is so interesting blog post. I am going to post another post tomorrow where I will link your this post give my notes on the top of it.

    Laerte – I loved this post, my gratitude.

  2. Pingback: SQL SERVER – Start Services or Stop Services with PowerShell – Answer to Question | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s