SQL SERVER – Start Services or Stop Services with PowerShell – Answer to Question

In a recent blog post, I posed an intriguing question about the relationship between PowerShell and T-SQL commands. You can check out the original post here: SQL SERVER – Start Services or Stop Services with PowerShell – Question to Readers?.

The question I asked was: Is the PowerShell command Stop-Service 'mysqlserver' equivalent to the T-SQL commands SHUTDOWN or SHUTDOWN WITH NOWAIT?

To be honest, I had no idea about the answer. Thankfully, my friend and PowerShell expert, Laerte Junior, took up the challenge to find the answer. He provided a detailed explanation backed by experiments and findings, which shed light on this interesting topic.

Laerte conducted multiple tests to explore the nuances of the question. He utilized trace flags 2592 and 3605 to log checkpoint activities in SQL Server’s error logs. This approach helped reveal the behavior of SQL Server when a shutdown is initiated through PowerShell versus T-SQL. I encourage everyone to read his blog post, as it offers valuable insights: Stop-Service is like the T-SQL SHUTDOWN or SHUTDOWN WITH NOWAIT?

After thorough testing, Laerte concluded that the PowerShell command STOP-SERVICE -Force is equivalent to the T-SQL SHUTDOWN command. This means it performs checkpoints for each database, ensuring a clean and graceful shutdown. However, it does not match the behavior of SHUTDOWN WITH NOWAIT, which terminates processes abruptly without checkpoints.

This discovery was a valuable learning experience for me. I’m thankful to Laerte for clarifying this topic. If you weren’t aware of this before, you’re not alone! The first step in learning is to admit what we don’t know. The next step is to actively seek knowledge.

Did you find this explanation helpful? Have you learned something new? If so, I’d love to hear your thoughts in the comments below.

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