Failed jobs being automatically retried?

[TakeshiKovacs]

Do you want to request a feature or report a bug? Bug, I think... What is the current behavior?

I am running some backups of SQL Server databases, via a function, within start-rsjob. When the backups are successful, it works as expected. I get a single backup for each database. When the backups encounter and error, they are run twice, I get two separate attempts to backup each database. I have ran a trace on the SQL Server and can see the multiple backup commands coming in.

If the current behavior is a bug, please provide the steps to reproduce and if possible a minimal demo of the problem

What is the expected behavior? I expect the command to fail and not be re-run/retried.

Which versions of Powershell and which OS are affected by this issue? Did this work in previous versions of our scripts?

Please provide a code example showing the issue, if applicable:

import-module poshrsjob

function getDBListSQL{

    $DBList = Invoke-SQLCmd -ServerInstance $Server -Query "SELECT name FROM sys.databases WHERE DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' and DATABASEPROPERTYEX([name], 'Recovery') <> 'Simple' ORDER BY name ASC"

    RETURN $DBList
}

$DBArray | Start-RSJob -name "Test" -Throttle 3 -FunctionsToImport backupTLOG, WriteLog -ScriptBlock {
Start-sleep -seconds (Get-Random -Minimum 1 -Maximum 3)
$backupcommand = "backup database " + $_.name + " to disk = 'C:\doesnotexist\" + $_.name + "'"
invoke-sqlcmd -serverinstance 'localhost' -query $backupcommand

} | wait-rsjob

[copdips]

could you please check if there're some duplicate names in $DBArray ?

and could you please try to run a failed backup in a standard non-threaded way, and check if the backup was executed twice ?

[MVKozlov]

btw, is invoke-sqlcmd thread safe ?

and.. in the beginning I see getDBListSQL function but do not see usage of it $DBArray = getDBListSQL ? $DBArray.count -eq ($DBArray|sort -un).count ?

[TakeshiKovacs]

Hi Both

Thanks for taking the time to help.

MVKozlov Yes, I use it to populate $DBArray, in trying to provide a simplified code example I missed that out, thanks for noting.

copdips, there are no duplicates, in my case there are 4 databases for testing, if I use a path that exists, each database gets a single backup as expected. I did however check your second suggestion and found it indeed does try to run it twice even outside start-rsjob. I had never encountered this before so thought the threading was the more likely issue.

The issue and patches can be found here: https://support.microsoft.com/en-us/help/4010159/fix-invoke-sqlcmd-cmdlet-executes-a-query-statement-multiple-times-if

I will install CU and check it resolves my issue. Again thank you both for your time!

Cheers Mat

[TakeshiKovacs]

I can confirm this issue was resolved by the patch. Thanks both!