Logging errors from a Workflow in a WorkFlow running Checkdb

Welcome Forums General PowerShell Q&A Logging errors from a Workflow in a WorkFlow running Checkdb

  • This topic has 1 reply, 2 voices, and was last updated 4 months ago by
    Moderator
    .
Viewing 1 reply thread
  • Author
    Posts
    • #177825
      Participant
      Topics: 42
      Replies: 171
      Points: 610
      Helping Hand
      Rank: Major Contributor

      I've written a script to run CheckDb on multiple databases concurrently across multiple servers. But I'm struggling to get any errors logged returned. I'd like to have all the errors in a PSCustomObject so I can have all errors for all databases on all servers returned in one object.  How can I do that ?

       

      Command to call the process:

      Start-CheckDb "server\DEV1", "server\DEV2"
      WorkFlow Start-CheckDb
      {
      
      Param(
      [string[]]$SqlServers
      )
      
      $SqlServers
      
      foreach -Parallel ($SqlServer in $SqlServers ) {
      
      WorkFlow WorkFlow-CheckDb
      {
      Param(
      [string]$SqlServer
      )
      
      $ErrorActionPreference= "Continue"
      $Databases = (Invoke-SqlCmd -ServerInstance $SqlServer -Query "SELECT name FROM sys.databases WHERE Database_id > 4 AND state = 0 AND user_access = 0 ORDER BY name") | ?{($_.Name -Match "csdev|cstest|csprod")} | `
      Select -ExpandProperty name
      
      Foreach -Parallel -throttlelimit 4 ($Database in $Databases) {
      Function Execute-CheckDB
      {
      Param (
      [string]$SqlServer ,
      [string]$Database
      )
      
      BEGIN {
      [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
      $output = @()
      }
      PROCESS {
      
      $Srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SqlServer
      $Db = $Srv.Databases[$Database]
      
      try {
      $Db.CheckTables([Microsoft.SqlServer.Management.Smo.RepairType]::None,
      [Microsoft.SqlServer.Management.Smo.RepairOptions]::NoInformationMessages)
      } catch {
      
      $err = $_.Exception
      while ( $err.InnerException )
      {
      $err = $err.InnerException
      $ErrMsg = $err.Message
      };
      Write-Host "$($Db.name) CheckDb on $SqlServer errored`r`n$ErrMsg" -ForegroundColor Red
      
      $output += [PSCustomObject] @{
      SqlInstance = $SqlServer
      Database = $($Db.name)
      Error = $ErrMsg
      }
      }
      
      }
      END {
      "CheckDb $($Db.name) completed on $SqlServer"
      Return $output
      }
      } # end Function Execute-CheckDB
      
      Execute-CheckDB -SqlServer $SqlServer -Database $Database
      
      } # Close Foreach CheckDB
      
      } # Close WorkFlow-CheckDb
      
      WorkFlow-CheckDB -SqlServer $SqlServer
      
      } # Close foreach
      
      }
      
    • #180426
      Moderator
      Topics: 3
      Replies: 172
      Points: 906
      Helping Hand
      Rank: Major Contributor

      Maintaining the objects inside the workflow between the reboots happen through saving the state of the flow to the disk at the end of each flow in the form of xml or json format, and retrieve it when it resumes.

Viewing 1 reply thread
  • The topic ‘Logging errors from a Workflow in a WorkFlow running Checkdb’ is closed to new replies.