SQL Server specialists

Starting the SQL Agent from the command line

If you’ve attempted to start the SQL Agent from the command prompt you likely would have received the following message:
It is possible to run the Agent, you just require arguments i.e.

  • -c which allows you to use the command prompt, so always required
  • -v optional verbose mode which includes the standard SQL Server Agent error log entries
  • -iinstancename if you are using a named instance

Below shows with -c used for a default instance
Yes there will probably be times when you’ll need to use the above to troubleshoot Agent issues.
CTRL + C will stop the Agent.

Pausing the SQL Server service

The Pause option on the SQL Server service account prevents new connections, but allows existing connections to continue operating.
When would you use this? Most likely during a patching or outage cycle to allow existing users to gracefully exit while preventing new connections.

Remote shutdown

The shutdown /i command has come in handy many a time and especially when you find that a server just doesn’t appear to be restarting and you’re unable to connect to it.
So if you find yourself in that situation, give this a try and see if it help to resolve the issue.


In case you missed it, AUTOGROW_ALL_FILES was added in SQL Server 2016 and tempdb has this enabled by default.
Further info is mentioned here.

What this means is that you should be extra careful that you don’t run out of disk space when suddenly all files need to grow.

SSMS SQL Server cmdlets

In SQL Server Management Studio 17.0 the PowerShell module was moved into the PowerShell gallery.
This means that you may see the following:
When you go to https://www.powershellgallery.com/ you currently see:
I don’t know about you, but it suddenly seems that a simple tweak to SSMS has become a headache for DBAs.
Performing a search gives 4 results and it looks like the matteot_msft is the correct one. Having company package under a users name doesn’t sound like a good idea to me, possibly this should be a team account?
So how do you download it?
lol mind blown, KISS appears to have gone out the window :)
If you’ve made it this far and have an internet connection the supplied method is:


Lets give it a try:
It’s now requesting NuGet provider version ‘’ or newer
Once completed you’ll see the folder and files
For the next step, I received the following message which requested for -Force to be used:
Once completed, the SSMS error message no longer occurs