In my previous blog, we found an error in the Event Log after the scheduled restart of the Windows Task Scheduler task for the Performance Monitor Data Collector Set.

Data collector set SQLAudit failed to start as WORKGROUP\xxxxx$ with error code 0x803000AA.

In this blog, I will outline how we can attach a Task to this event, which will execute a script to start the original task.

If you haven’t already got it open, open Event Viewer and navigate to Application and Services Logs\Microsoft\Windows\Diagnosis-PLA and select the Operational log.

Select the event with the error above, Right Click, and select Attach Task To This Event…
Fill out details for the Name and the Description and select Next
In the When Event Is Logged screen, just select Next
For Action, select Start a program and select Next
In the Start a Program option, enter Powershell.Exe for Program/script: and leave the other fields blank. We’ll fill those out later.
Select Next
Click on the Open the Properties dialog for this task when I click Finish checkbox and click on Finish
The following will be displayed.

Select the Triggers tab, and click on Edit
In Settings, select Custom and then select New Event Filter…
Select the XML tab, and click on the Edit query manually checkbox.
Select Yes on the warning message.

In the query box, enter the following (I use a custom query as I only want the task to trigger for this specific error code, not the EventID.)

<QueryList>
  <Query Id="0" Path="Microsoft-Windows-Diagnosis-PLA/Operational">
    <Select Path="Microsoft-Windows-Diagnosis-PLA/Operational">
	*[System[(EventID=1004)]]
	and *[UserData/DataCollectorSetStartFailed/Name="SQLAudit"]
                and *[UserData/DataCollectorSetStartFailed/Error=2150629546]
    </Select>
  </Query>
</QueryList>

Click Ok twice to get back to the properties window.

All we need to do now, is configure the action to take when the alert triggers the task.

Function StartDCS([System.Object]$DCS) 
{
    For ($wait=1; $wait -le 30; $wait++) { # check status for 30 seconds 

    If ($DCS.Status -eq 0) { # Stopped
        $DCS.Start($true) 
        Start-Sleep -Seconds 1
        }
    }
}

$ErrorActionPreference="SilentlyContinue"
Stop-Transcript | out-null
$ErrorActionPreference = "Continue"
Start-Transcript -path C:\Scripts\Perfmon-Start.txt -append
#Transcript location can be configured as required.

$Server = $env:ComputerName
$DCSName = "SQLAudit";
$DCS = New-Object -COM Pla.DataCollectorSet
$DCS.Query($DCSName,$Server)

StartDCS $DCS

If ($DCS.Status -ne 1) { #Not Running
    Write-Host "Failed to start SQLAudit"
}

Stop-Transcript | out-null

If you find your collectors take longer than 30 seconds to stop, then update the value in the ‘For’ statement.

Save this as a file on your server, and then configure the ‘Action’ in the Windows Task to call this script, by setting the Arguments and Start In details.
In Add arguments (optional): you can enter

-ExecutionPolicy Bypass -file "C:\Scripts\Perfmon-Start.ps1"

And in the Start in folder (optional): just put the folder where the script is saved. (C:\Scripts)

I would highly recommend looking into having this script signed to prevent any security risks associated with storing scripts on the server.

Note: Windows Server 2012 R2 was used when configuring this task. Other versions may be a little different.