Start-Job PowerShell two jobs getting created

Question

I want to execute set of un-contained stored procedures, as jobs, to see if there are any issues in the code after making the database as contained database. After some time, I want to see whether the stored procedures are successful or not. But, I see that instead of one job for a stored procedure, two jobs are getting created. How to avoid the creation of two jobs?

$unContainedSProcs = Import-Csv -Path C:\DFLog\UnContained_Parameterless_SPs.csv
$batchSize = 50
$currentCompletedIdx = 0
$jobIds = @()
$scriptToExecute = {
    Param($storedProcToExecute)
    Invoke-Sqlcmd -Query "Exec $storedProcToExecute" -ServerInstance "<<ServerInstance>>" -Database "<<Database>>" -QueryTimeout 120
}

while ($currentCompletedIdx -le $unContainedSProcs.Length) {
    for ($i= 0; $i -le $batchSize; $i++) {
        $job = Start-Job -Name $unContainedSProcs[$i].UnContainedSProcName -ScriptBlock $scriptToExecute -ArgumentList $unContainedSProcs[$i].UnContainedSProcName
        $jobIds += $job.Id
        ++$currentCompletedIdx
    }
}

When I see the jobs list, using Get-Job, I see two jobs:

+----+--------------+------------------+-----------+---------------+--------------+------------------------------+
| Id |     Name     | PSJobTypeName    |   State   |   HasMoreData |   Location   |           Command            |
+----+--------------+------------------+-----------+---------------+--------------+------------------------------+
|  1 |   dbo.SPName | BackgroundJob    | Completed |  True         |    localhost |    param($storedProcToExe... |
| 41 |  Job41       | BackgroundJob    | Completed |  True         |    localhost |    param($storedProcToExe... |
+----+--------------+------------------+-----------+---------------+--------------+------------------------------+

my understanding is that PoSh starts a parent job and one or more child jobs when you use Start-Job. i don't know why, tho. [blush] — Lee_Dailey
@Lee_Dailey, No. Not due to that. It is due to -le (less than or equal to) condition. I have figure it out. when I use -lt (less than) in the for loop, it is working fine. I will add answer. — Venkataraman R
PS do create 2 jobs but the 2nd one is usually not visible on Get-Job. Just to have the basics covered, I hope you have made sure that the other job is not from an earlier run, correct? You can do get-job | remove-job -force to be sure. I tried Start-job on an invoke-sqlcmd payload and only got 1 job. So this is hard for me to explain. — Sid

Venkataraman R Venkataraman R · Accepted Answer · 2019-06-13T12:39:19

I have figured it out. It is due to wrong definition of for loop condition. When the number of stored procedures is 1 and for loop is running twice: 0, 1. It is due to -le (less than or equal to) Length of $unContainedSProcs(1).

There is only one entry [0] in $unContainedSProcs. For the first run, it is fine. For second run, there is no entry[1] in $unContainedSProcs. So, empty job is getting created. After changing -le to -lt, there is only one job created.

From:

for ($i= 0; $i -le $batchSize; $i++) {

To:

for ($i= 0; $i -lt $batchSize; $i++) {

Start-Job PowerShell two jobs getting created

1 Answers