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.
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.
$servers = Get-DbaRegisteredServer -SqlInstance "localhostsql2017"
$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
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.
Next step:
I want to keep this simple, so I am going to assign the output returned from our filter to a new variable.
$failedOutput = $failedJobs | Where-Object { ( $_.LastRunOutcome -eq "Failed") -AND ($_.Category -notlike "REPL*") }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.
Final Step:
It is time to store the failed job output into the database table.
Write-DbaDbTableData -SqlInstance "localhostsql2017" -Database DBA -InputObject $failedOutput -Table FailedJobs -AutoCreateTable
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:
- 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
Conclusion:
As easy as that we have stored the results of running a PowerShell command to a SQL Database Table.
I hope this sparks some thoughts in your DBA mind of how you can use these tools to make your SQL Server Environment better, with simple to build scripts.
If you found this blog handy, please use the Subscribe section at the top of the screen to join my newly created mailing list. I hope to start delivering content via the mailing list soon. 
The post Failing SQL Agent Jobs – Part 4 appeared first on GarryBargsley.com.