Logging errors from a Workflow in a WorkFlow running Checkdb

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

}

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.