Microsoft.SqlServer.Management.Smo.Server.Jobs.Job.EnumHistory() is limited to 100 rows

Question

I am trying to write a script to scan for Sql Maintenance Task failures - see script below. I appear to be unable to process more than 100 entries using EnumHistory(). Does anyone have a way around this?

Param(
  [int]$days="30"  # this hardly matters since EnumJobHistory is limited to 100 rows :-(
)

#http://powershell.com/cs/blogs/tobias/archive/2010/01/13/cancelling-a-pipeline.aspx
filter Stop-Pipeline([scriptblock]$condition = {$true}) 
{$_
    if (& $condition) {continue}
}

cls
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$instances = Get-Content "DailyMaintenanceMMCServerList.txt"

#loop through each instance
 foreach ($instance in $instances)
 {

    # Create an SMO connection to the instance
    $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
    $instance

    #get all the jobs on the server
    $jobs = $srv.JobServer.Jobs

    # Avoid exception on some servers?
    if (!$jobs.Count) 
    {
        continue
    }

    #go through each job and find failures in the job history
    $jobs |  % {
        do 
        {   $job = $_; $count = 0; 
            $_.EnumHistory() | 
            Stop-Pipeline { $_.Rundate -lt  [datetime]::Today.AddDays(-$days) } |
            #? {$_.Message -notlike "*succeeded*" } |
            % { " " + ++$count + " " + $job.Name + " " + $_.RunDate + " " + ($_.Message).Substring(0,20) }
        } while ($false)
    }
 }

As pointed out by Ben Thul, the maximum number of rows of history kept is configured by server instance:

Job History Limits

Duh! Please make this the answer so I can mark it! Thanks! – David Rogers Feb 12 '14 at 18:37 — David Rogers, Feb 12 '14 at 18:37

score 1 · Accepted Answer · answered Feb 13 '14 at 14:37

Check how much history the Agent is configured to keep. In powershell, you can get this from the MaximumJobHistoryRows in the JobServer object. Or right click on the agent in SSMS and look at "history". My guess is that it's only configured to keep 100 per job.

Microsoft.SqlServer.Management.Smo.Server.Jobs.Job.EnumHistory() is limited to 100 rows

1 Answers1