Stop-DbaService add "Safely" parameter when using -Type Agent

[wsuhoey]

Summarize Functionality

I had a need to shutdown SQL Server Agent, but only when there are no Agent Jobs running. So I had this thought of having a loop check the count of jobs running, and when the .Count is 0, then do Stop-DbaService.

I had asked this question in the Slack:

i have a vision in my brain of what i want to accomplish, but i'm not sure which powershell loop construct i need
the goal is to "safely" shutdown SQL Server Agent, and by "safely" i mean no jobs are running.
my thought was to use Get-DbaRunningJob in some kind of loop checking if the result is 0 running jobs;
if any jobs are running then wait X seconds and check again.
if 0 jobs are running then do Stop-DbaService -Type Agent
so which powershell loop type do i need for this?
psuedocode welcome

With the help of @niphlod and @ben-thul and @alevyinroc , we came up with a simple (not foolproof) way of doing this:

try {
        while ( (Get-DbaRunningJob -SqlInstance $server -EnableException).Count -GT 0 )
        {
            Start-Sleep -Seconds $waitseconds
        }
        Stop-DbaService -ComputerName $server -Type Agent -Verbose #EnableException
    }
catch { Write-Error "Failed! $($error[0])" }

So I thought it would be cool to have something like a -Safely parameter when used with -Type Agent on Stop-DbaService, that uses the above count logic to check in a loop for X seconds (specified by user, perhaps via -Wait and also implementing a -MaxTimeout (suggested by Niph).

Naturally, someone with better powershell skills could implement better error handling etc. better design etc. proper indents & formatting etc. 😅

Is there a command that is similiar or close to what you are looking for?

Yes

Technical Details

try {
        while ( (Get-DbaRunningJob -SqlInstance $server -EnableException).Count -GT 0 )
        {
            Start-Sleep -Seconds $waitseconds
        }
        Stop-DbaService -ComputerName $server -Type Agent -Verbose #EnableException
    }
catch { Write-Error "Failed! $($error[0])" }

For the Is there a command that is similar or close to what you are looking for? question: Start-DbaAgentJob has kind of similar logic via -Wait and -WaitPeriod

[wsmelton]

How long should we wait? If you have a job running an index rebuild online that was working on a 2GB PK that could take hours to roll back if we kill that job.

I would recommend having the parameter check if jobs are running, if they are throw a warning that jobs are running and then stop. The user should know which jobs can be stopped safely and which ones cannot be.

[wsuhoey]

I'm confused by your question, I didn't say anything about killing jobs?

[wsmelton]

You state waiting until a job is no longer running. Whether we kill the job or not same issue applies.

If I have a job running that is hung or doing a rollback the job would be in a non stopped state until it finished.

I don't see a reason to wait, if they provide the flag and we find jobs running then throw a warning and stop.

[wsuhoey]

That's where a -Wait and -MaxTimeout would come into play (Niph's idea to have a max). We wait for as long as the user specifies between checks, up to the max the user specifies.