Here we are, another Tuesday!! It is time for our Fourth and final installment in the Failing SQL Agent Jobs series. This week, we will cover how to store SQL Agent Job Failures to a database for tracking and review.
This example shows that across four servers we have one job failure on the DEV2016 instance. That is good information to have. Now, lets write that information to a table in our DBA database.
Notice the
$failedOutput variable was assigned the data we collected with failed job information. This will allow us to not see mixed job information collected from step one.
As you can see we are using the
Write-DbaDbTableData command to do the work of inserting data. I will briefly explain the parameters being used:
First step:
This step we will re-use some code from our first blog in this series. This code will load a variable with our Server List from the CMS and then get all the current statuses of the SQL Server Agent Jobs. The last step will be to filter down to just SQL Server Agent Jobs Failures.|
1 2 3 |
$servers = Get-DbaRegisteredServer -SqlInstance "localhost\sql2017" $failedJobs = Get-DbaAgentJob -SqlInstance $servers $failedJobs | Where-Object { ( $_.LastRunOutcome -eq "Failed") -AND ($_.Category -notlike "REPL*") } | Format-Table SqlInstance, Name, LastRunDate, LastRunOutcome, Category, IsEnabled, HasSchedule -AutoSize |
Next step:
I want to keep this simple, so I am going to assign the output returned from our filter to a new variable.|
1 |
$failedOutput = $failedJobs | Where-Object { ( $_.LastRunOutcome -eq "Failed") -AND ($_.Category -notlike "REPL*") } |
Final Step:
It is time to store the failed job output into the database table.|
1 |
Write-DbaDbTableData -SqlInstance "localhost\sql2017" -Database DBA -InputObject $failedOutput -Table FailedJobs -AutoCreateTable |
- Database = The database we want to insert the records
- InputObject = This is the variable that contains the data we plan to insert
- Table = Name of the table to insert the records
- AutoCreateTable = This will create the table if it does not exist or just insert the record(s) if the table already exists
- Truncate = Not shown in this example, you can use truncate if you want to clear the table before each run to only store current failures
