Check for failed SQL Agent Jobs with Powershell
Checking for failed SQL Agent jobs should be part of any DBA workplan. Here’s another Powershell script that makes checking the last run outcome easy on multiple SQL Servers. To run this script you need to create a list of your SQL Servers in a text file called sqlservers.txt. Place this text file in your user profile directory, C:\Users\Rhys on my laptop.
# Load SMO extension [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null; # Get List of sql servers to check $sqlservers = Get-Content "$Env:USERPROFILE\sqlservers.txt"; # Loop through each sql server from sqlservers.txt foreach($sqlserver in $sqlservers) { # Create an SMO Server object $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver; # Jobs counts $totalJobCount = $srv.JobServer.Jobs.Count; $failedCount = 0; $successCount = 0; # For each jobs on the server foreach($job in $srv.JobServer.Jobs) { # Default write colour $colour = "Green"; $jobName = $job.Name; $jobEnabled = $job.IsEnabled; $jobLastRunOutcome = $job.LastRunOutcome; # Set write text to red for Failed jobs if($jobLastRunOutcome -eq "Failed") { $colour = "Red"; $failedCount += 1; } elseif ($jobLastRunOutcome -eq "Succeeded") { $successCount += 1; } Write-Host -ForegroundColor $colour "SERVER = $sqlserver JOB = $jobName ENABLED = $jobEnabled LASTRUN = $jobLastRunOutcome"; } # Writes a summary for each SQL server Write-Host -ForegroundColor White "========================================================================================="; Write-Host -ForegroundColor White "$sqlserver total jobs = $totalJobCOunt, success count $successCount, failed jobs = $failedCount."; Write-Host -ForegroundColor White "========================================================================================="; } |
The output provided shows jobs with a last run status of “Succeeded” in green and “Failed” in red. A summary is provided for each individual SQL Server.
I tried using this and it only returns like as if the answers were empty. It all stays green and prints nicely but it’s as if it doesn’t find any jobs? I have several servers and several jobs so I don’t know why it isn’t working?
I’ll need to know what is being printed out before I can have a proper guess.
If there’s green text then it’s finding jobs (the green text printed is for successful jobs).
got it to work! I had previously had a list of servers for osql but that required quotes around the server name. i removed that and this script works great. thanks
Hi Royce,
Good stuff. If you want to use the same text file for osql and powershell you could add something to remove the quotes from server names, Something like
$sqlserver = $sqlserver.Replace('"', '');
should do it.
Rhys
thanks for the reply.
I have a new challenge. I want to use an xml file to contain all the info for a database application. I want to make using this app very easy and so I want to build it in such a way that the only adjustments anyone needs to make is easily contained in the xml file. We have entries like . Currently I have another file that reads the xml and sets a $ variable like $path_fullbackup=[xml]..getattribute(“path_fullbackup”)
I would like to write some kind of function that would bring into scope a new variable with the same name as whatever the node is in the xml. so in other words to avoid hard coding looking for certain attribute names and defining what the $ variable should be.
….
I don’t really know how to do it so I have some pseudo code that is close to the idea:
#Iterate through the Path nodes
Foreach ($Path in $paths)
{
ForEach $PathAttrib in $Path.Attributes
{
$AttributeName = $PathAttrib.Name
$AttributeValue = $PathAttrib.Value
}
$AttribHash.Add($AttributeName, $AttributeValue)
}
Is this even possible?
Not something I’ve ever done but it looks simple enough. Checkout Powershell and XML Configuration Files
Cheers,
Rhys
hi,
how to print the overview output first and all job details after that. what i want is
first shud be
servername total jobs=10 success count=10 failed jobs=0
then
all job details one by one.. how to acheive this??
and one more thing how to mail this entire output???
Thanks in Advance
Hi Vinay,
Very easily. Just append the job output to a variable and then output it after your title.
i.e.
Instead of
Write-Host -ForegroundColor $colour "SERVER = $sqlserver JOB = $jobName ENABLED = $jobEnabled LASTRUN = $jobLastRunOutcome";
Do..
$output += "SERVER = $sqlserver JOB = $jobName ENABLED = $jobEnabled LASTRUN = $jobLastRunOutcome";
Don’t forget to reset $output for each server.
See this for emailing with Powershell
Cheers,
Rhys
Thanks for a brilliant script. I have some sql jobs that are disabled but your script still returns their staus as ‘enabled’
Hi Dave,
Mmmmm, it shouldn’t because I’m reading the property for each job at $job.IsEnabled;
Perhaps you can try resetting these at the bottom of the foreach loop. Perhaps posting the full output might help.
Cheers,
Rhys