Azure Pack Automation (SMA) Get-SMAJobOutput fails

I recently ran into an issue where regardless of the method used, I was unable to get any job output from Service Management Automation.

Get-SMAJobOutput -JobID '5c773933-5a9b-4021-a793-768b2efd0165' -WebServiceEndPoint $WebServiceEndpoint -Stream Output

simply returned:

Get-SmaJobOutput : The job 5c773933-5a9b-4021-a793-768b2efd0165 cannot be found: System.Data.Services.Client.DataServiceClientException: <?xml version="1.0" 
encoding="utf-8" standalone="yes"?><error xmlns=""><code></code><message xml:lang="en-US">An error 
occurred while executing the command definition. See the inner exception for details.</message></error>
   at System.Data.Services.Client.BaseAsyncResult.EndExecute[T](Object source, String method, IAsyncResult asyncResult)
   at System.Data.Services.Client.QueryResult.EndExecuteQuery[TElement](Object source, String method, IAsyncResult asyncResult)
At line:1 char:1
+ Get-SmaJobOutput -Id $id -WebServiceEndpoint $webserviceendpoint
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-SmaJobOutput], InvalidOperationException
    + FullyQualifiedErrorId : System.InvalidOperationException,Microsoft.SystemCenter.ServiceManagementAutomation.GetSmaJobOutput

Note that this is not the issue where the output fails with the same error if using the wrong -Stream case.

Additionally, getting the job output from the Azure Pack admin site, simply returned a red exclamation mark.

Next, I adapted a REST based function to get the output from the REST api (originally posted here:

# Original script from
# Modified by Christopher Keyaert (

 Function Get-SMARESTJobOutput{
# Ignore SSL Self-Signed certificate  
    [System.Net.ServicePointManager]::CertificatePolicy = new-object IgnoreSelfSignedCertificate
add-type @"
    using System.Net;
    using System.Security.Cryptography.X509Certificates;
        public class IgnoreSelfSignedCertificate : ICertificatePolicy {
        public IgnoreSelfSignedCertificate() {}
        public bool CheckValidationResult(
            ServicePoint sPoint, X509Certificate cert,
            WebRequest wRequest, int certProb) {
            return true;
"@ -ErrorAction SilentlyContinue
    [System.Net.ServicePointManager]::CertificatePolicy = new-object IgnoreSelfSignedCertificate
    $SMAServer = "${WebServiceEndpoint}:9090"
    $VerbosePreference = "Continue"
    #$VerbosePreference = "SilentlyContinue"
    Write-Verbose ""

    $URI =  "$SMAServer/00000000-0000-0000-0000-000000000000/Jobs(guid'" + $ID  + "')"
    $Response = if($Credentials){Invoke-RestMethod -Uri $URI  -Method Get -Credential $credentials}else{Invoke-RestMethod -Uri $URI  -Method Get} 
    $JobStatus = $ 
    Write-Verbose "Job Status = $JobStatus"
    $URI =   "$SMAServer/00000000-0000-0000-0000-000000000000/JobStreams/GetStreamItems?jobId='" + $ID +"'&streamType='$Stream' "
        $Result = if($Credentials){Invoke-RestMethod -Uri $URI  -Method Get -Credential $credentials}else{Invoke-RestMethod -Uri $URI  -Method Get}
    }Catch [exception]{
        $Exception = $_
    Write-verbose "StreamText = $($"
    $outputname = "Stream${Stream}"
    New-object -TypeName PSCustomObject -ArgumentList @{'ID'=$ID;'Status'=$JobStatus;$outputname=$}

This returned the same error as Get-SMAJobOutput (An error
occurred while executing the command definition. See the inner exception for details)

The more I researched this, the more it smelled like a performance issue querying the database.
After doing a bit of digging, I found that my [SMA].[Stream].JobStreams table was ~17,000,000 rows. Using the following command, I dropped the job history down to 30 days (we had previously set it to 90):

Set-SmaAdminConfiguration -PurgeJobsOlderThanCountDays 30 -WebServiceEndpoint $webserviceendpoint

This took 8 hours to run and dropped the table down to 9,000,000 rows. The issue still remained, so I dropped it further to 25 days and also disabled debugging and process logging on all runbooks. This dropped the table down to 7,500,000 rows. Now the issue occurred ~75% of the time….Progress!

Since I had now verified that it was an SQL performance issue, I turned to the Activity Monitor of SQL Studio.
Sure enough, the JobStreams table did not have an index usable to query the data required by the Get-SMAJobOutput powershell command. Adding the following index dropped the query time to instantaneous:

ON [Stream].[JobStreams] ([TenantId],[JobId])

The issue was now resolved! Please note that this fix may not be supported by Microsoft. Please use at your own discretion.