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 contains 1 reply, has 2 voices, and was last updated by

 
Participant
2 weeks, 6 days ago.

  • Author
    Posts
  • #177825

    Participant
    Topics: 39
    Replies: 158
    Points: 517
    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

    Participant
    Topics: 1
    Replies: 96
    Points: 514
    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.

You must be logged in to reply to this topic.