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
}