Runspace with foreach loop

Welcome Forums General PowerShell Q&A Runspace with foreach loop

Viewing 9 reply threads
  • Author
    Posts
    • #243503
      Participant
      Topics: 2
      Replies: 6
      Points: 39
      Rank: Member

      I have a question on the runspace, I am also trying to implement this, but using a foreach loop and with a runspace, can this be done? I have a foreach loop for each database, then i am doing step 3 in the foreach loop (3. Create runspace and start it) like so:

      this does work, However the problem or error I am getting is
      Exception calling “EndInvoke” with “1” argument(s): “The WriteObject and
      WriteError methods cannot be called from outside the overrides of the
      BeginProcessing, ProcessRecord, and EndProcessing methods, and they can only
      be called from within the same thread. Validate that the cmdlet makes these
      calls correctly, or contact Microsoft Customer Support Services.

      which when i see the backup is happening for each database, it seems to overwrite one over the other, and not backing all of them up parallel… do you by chance know what i may be doing wrong?

      thanks in advance

      • This topic was modified 2 months, 1 week ago by kvprasoon. Reason: Code formatting
    • #243518
      Senior Moderator
      Topics: 9
      Replies: 1311
      Points: 4,794
      Helping Hand
      Rank: Community Hero

      You will have to post the code where you dispose the runspace, I feel like the problem is there…

    • #243530
      Participant
      Topics: 2
      Replies: 6
      Points: 39
      Rank: Member

      Here is the dispose code part, sorry about that and thank you, below is the code:

      # 6 Wait for runspaces to complete
      while ($runspaces.Status.IsCompleted -notcontains $true) {}

      # 7.1 Cleanup runspaces
      foreach ($runspace in $runspaces) {
      [void]$runspace.Pipe.EndInvoke($runspace.Status) # EndInvoke method retrieves the results of the asynchronous call
      $runspace.Pipe.Dispose()
      }

      # 7.2 Cleanup runspace pool
      $pool.Close()
      $pool.Dispose()

      # Cleanup SQL Connections
      [System.Data.SqlClient.SqlConnection]::ClearAllPools()

      • This reply was modified 2 months, 1 week ago by siten030892.
      • This reply was modified 2 months, 1 week ago by siten030892.
    • #243539
      Participant
      Topics: 2
      Replies: 6
      Points: 39
      Rank: Member

      Sorry yes, here is the dispose code part below:

      # 6 Wait for runspaces to complete
      while ($runspaces.Status.IsCompleted -notcontains $true) {}

      # 7.1 Cleanup runspaces
      foreach ($runspace in $runspaces) {
      [void]$runspace.Pipe.EndInvoke($runspace.Status) # EndInvoke method retrieves the results of the asynchronous call
      $runspace.Pipe.Dispose()
      }

      # 7.2 Cleanup runspace pool
      $pool.Close()
      $pool.Dispose()

      # Cleanup SQL Connections
      [System.Data.SqlClient.SqlConnection]::ClearAllPools()

    • #243542
      Participant
      Topics: 2
      Replies: 6
      Points: 39
      Rank: Member

      Sorry i realized, probably showing everything is more beneficial than cuts and pieces of it, so here is the entire Code below:

      # 2 Create reusable scriptblock
      $scriptblock = {
      Param(
      [string] $DBName
      ,[string] $BackupType
      ,[string] $BackupPath
      )

      Process
      {
      $Date = Get-Date -Format "yyyy_MM_dd_HH_mm"
      $StringDate = $Date.ToString()

      If($BackupType -eq 'Trn')
      {
      $SQLStatement = "BACKUP LOG [" + $DBName + "] TO  DISK = N'" + $BackupPath + "\"+ $DBName + "_" + $StringDate + ".trn' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10"
      }
      ElseIf($BackupType -eq 'Diff')
      {
      $SQLStatement = "BACKUP DATABASE [" + $DBName + "] TO  DISK = N'" + $BackupPath + "\"+  $DBName + "_" + $StringDate + ".Diff' WITH  DIFFERENTIAL , NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10"
      }
      Else
      {
      $SQLStatement = "BACKUP DATABASE [" + $DBName + "] TO  DISK = N'" + $BackupPath + "\"+  $DBName + "_" + $StringDate + ".bak' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10"
      }

      Invoke-Sqlcmd -Query $SQLStatement -ServerInstance "Server001"
      }

      }

      # 4 Add to Runspace Pool
      # 5 Add runspace to runspace collection
      ForEach ($DB in $Databases)
      {
      #$script = "scriptblock -DBName "" + $DB.name + "" -BackupType "" + $BackupType + "" -BackuPath "" + $BackupPath + """

      # 3 Create runspace and start it
      $runspace = [PowerShell]::Create()#.AddScript($scriptblock).AddArgument($DB.name).AddArgument($BackupType.ToString()).AddArgument($BackupPath.ToString())
      #$runspace = [PowerShell]::Create()
      [void]$runspace.AddScript($scriptblock)
      [void]$runspace.AddArgument($DB.name)
      [void]$runspace.AddArgument($BackupType.ToString())
      [void]$runspace.AddArgument($BackupPath.ToString())
      $runspace.RunspacePool = $pool
      $runspaces += [PSCustomObject]@{ Pipe = $runspace; Status = $runspace.BeginInvoke() }

      #BackupProcess -DBName $DB.name.ToString() -BackupType $BackupType.ToString() -BackupPath $BackupPath.ToString()
      #$DB.name

      #Write-Host $("{0} is currently the primary replica for the {1} database. Backing Up." -f [System.Environment]::MachineName, $DB.name)

      # TODO: Backup and Upload To S3
      #Write-Host "Execution completed"
      }

      # 6 Wait for runspaces to complete
      while ($runspaces.Status.IsCompleted -notcontains $true) {}

      # 7.1 Cleanup runspaces
      foreach ($runspace in $runspaces) {
      [void]$runspace.Pipe.EndInvoke($runspace.Status) # EndInvoke method retrieves the results of the asynchronous call
      $runspace.Pipe.Dispose()
      }

      # 7.2 Cleanup runspace pool
      $pool.Close()
      $pool.Dispose()

      # Cleanup SQL Connections
      [System.Data.SqlClient.SqlConnection]::ClearAllPools()

    • #243545
      Participant
      Topics: 2
      Replies: 6
      Points: 39
      Rank: Member

      So an update, I did get passed the error now, however, the problem now i am facing is, during the backup process, it starts either deleting, or doesnt finish and disposes of the thread/pool before it finishes, it does however finish some, but the first few doesnt finish and deletes or removes the backups, here is the entire code, maybe i am calling on Dispose to soon, I did even try and put an if statement in there (If $runspace.Status.IsCompleted), so if true, then i would dispose of it, however when i add that in, it doesnt even get to do any backups, so i removed that and kept the foreach within a foreach, but again doesnt seem to do or finish all the backups… below is the code, any thoughts?

       

      # 1 Create and open runspace pool
      $pool = [RunspaceFactory]::CreateRunspacePool(1,5)
      $pool.ApartmentState = “MTA”
      $pool.Open()
      $runspaces = @()

      # 2 Create reusable scriptblock
      $scriptblock = {
      Param(
      [string] $DBName
      ,[string] $BackupType
      ,[string] $BackupPath
      )

      Process
      {
      $Date = Get-Date -Format “yyyy_MM_dd_HH_mm”
      $StringDate = $Date.ToString()

      If($BackupType -eq ‘Trn’)
      {
      $SQLStatement = “BACKUP LOG [” + $DBName + “] TO DISK = N'” + $BackupPath + “\”+ $DBName + “_” + $StringDate + “.trn’ WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10”
      }
      ElseIf($BackupType -eq ‘Diff’)
      {
      $SQLStatement = “BACKUP DATABASE [” + $DBName + “] TO DISK = N'” + $BackupPath + “\”+ $DBName + “_” + $StringDate + “.Diff’ WITH DIFFERENTIAL , NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10”
      }
      Else
      {
      $SQLStatement = “BACKUP DATABASE [” + $DBName + “] TO DISK = N'” + $BackupPath + “\”+ $DBName + “_” + $StringDate + “.bak’ WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10”
      }

      Invoke-Sqlcmd -Query $SQLStatement -ServerInstance “Server001”
      }

      }

      # 4 Add to Runspace Pool
      # 5 Add runspace to runspace collection
      ForEach ($DB in $DBList)
      {
      #$script = “scriptblock -DBName “” + $DB.name + “” -BackupType “” + $BackupType + “” -BackuPath “” + $BackupPath + “””

      # 3 Create runspace and start it
      $runspace = [PowerShell]::Create()#.AddScript($scriptblock).AddArgument($DB.name).AddArgument($BackupType.ToString()).AddArgument($BackupPath.ToString())
      #$runspace = [PowerShell]::Create()
      [void]$runspace.AddScript($scriptblock)
      [void]$runspace.AddArgument($DB.name)
      [void]$runspace.AddArgument($BackupType.ToString())
      [void]$runspace.AddArgument($BackupPath.ToString())
      $runspace.RunspacePool = $pool
      $runspaces += [PSCustomObject]@{ Pipe = $runspace; Status = $runspace.BeginInvoke() }

      foreach ($runspace in $runspaces) {

      #[void]$runspace.Pipe.EndInvoke($runspace.Status) # EndInvoke method retrieves the results of the asynchronous call
      $runspace.Pipe.Dispose()

      }
      }

      $pool.Close()
      $pool.Dispose()

      # Cleanup SQL Connections
      [System.Data.SqlClient.SqlConnection]::ClearAllPools()

       

      • This reply was modified 2 months, 1 week ago by siten030892. Reason: remove some stuff
    • #243638
      Senior Moderator
      Topics: 9
      Replies: 1311
      Points: 4,794
      Helping Hand
      Rank: Community Hero

      For better readability, please format your code be reading below thread.

      https://powershell.org/forums/topic/read-me-before-posting-youll-be-glad-you-did/

    • #243650
      Participant
      Topics: 2
      Replies: 6
      Points: 39
      Rank: Member

      Redone per format:

       

      So an update, I did get passed the error now, however, the problem now i am facing is, during the backup process, it starts either deleting, or doesnt finish and disposes of the thread/pool before it finishes, it does however finish some, but the first few doesnt finish and deletes or removes the backups, here is the entire code, maybe i am calling on Dispose to soon, I did even try and put an if statement in there (If $runspace.Status.IsCompleted), so if true, then i would dispose of it, however when i add that in, it doesnt even get to do any backups, so i removed that and kept the foreach within a foreach, but again doesnt seem to do or finish all the backups… below is the code, any thoughts?

      # 1 Create and open runspace pool $pool = [RunspaceFactory]::CreateRunspacePool(1,5) $pool.ApartmentState = “MTA” $pool.Open() $runspaces = @() # 2 Create reusable scriptblock $scriptblock = { Param( [string] $DBName ,[string] $BackupType ,[string] $BackupPath ) Process { $Date = Get-Date -Format “yyyy_MM_dd_HH_mm” $StringDate = $Date.ToString() If($BackupType -eq ‘Trn’) { $SQLStatement = “BACKUP LOG [” + $DBName + “] TO DISK = N’” + $BackupPath + “\”+ $DBName + “_” + $StringDate + “.trn’ WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10” } ElseIf($BackupType -eq ‘Diff’) { $SQLStatement = “BACKUP DATABASE [” + $DBName + “] TO DISK = N’” + $BackupPath + “\”+ $DBName + “_” + $StringDate + “.Diff’ WITH DIFFERENTIAL , NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10” } Else { $SQLStatement = “BACKUP DATABASE [” + $DBName + “] TO DISK = N’” + $BackupPath + “\”+ $DBName + “_” + $StringDate + “.bak’ WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10” } Invoke-Sqlcmd -Query $SQLStatement -ServerInstance “Server001” } } # 4 Add to Runspace Pool # 5 Add runspace to runspace collection ForEach ($DB in $DBList) { #$script = “scriptblock -DBName “” + $DB.name + “” -BackupType “” + $BackupType + “” -BackuPath “” + $BackupPath + “”” # 3 Create runspace and start it $runspace = [PowerShell]::Create()#.AddScript($scriptblock).AddArgument($DB.name).AddArgument($BackupType.ToString()).AddArgument($BackupPath.ToString()) #$runspace = [PowerShell]::Create() [void]$runspace.AddScript($scriptblock) [void]$runspace.AddArgument($DB.name) [void]$runspace.AddArgument($BackupType.ToString()) [void]$runspace.AddArgument($BackupPath.ToString()) $runspace.RunspacePool = $pool $runspaces += [PSCustomObject]@{ Pipe = $runspace; Status = $runspace.BeginInvoke() } foreach ($runspace in $runspaces) { #[void]$runspace.Pipe.EndInvoke($runspace.Status) # EndInvoke method retrieves the results of the asynchronous call $runspace.Pipe.Dispose() } } $pool.Close() $pool.Dispose() # Cleanup SQL Connections [System.Data.SqlClient.SqlConnection]::ClearAllPools() [/crayon]
    • #243656
      js
      Participant
      Topics: 32
      Replies: 842
      Points: 2,690
      Helping Hand
      Rank: Community Hero

      In powershell 7, you can use foreach-object -parallel instead, and it’s the same idea.

    • #243659
      js
      Participant
      Topics: 32
      Replies: 842
      Points: 2,690
      Helping Hand
      Rank: Community Hero

      How do you edit your own posts? I was going to add start-threadjob as well.

Viewing 9 reply threads
  • You must be logged in to reply to this topic.