Find failed SQL jobs with powershell

This weekend I went to SQL Saturday in Auckland. It was nice to interact with other DBAs again.  Kent Chenery (blog | twitter) did a nice session on powershell for n00bs and it got me thinking that I need to start posting some of my powershell stuff up here.

When I started my current job I was given a handover document that had a list of standard operating procedures, some links to some useful info, and a “daily morning checks” list. When I first read this list I was amazed. It went something like this:

  1. Log on to server A
  2. Open up management studio
  3. Open up SQL Agent’s job
  4. Order by last outcome
  5. Rerun the jobs that failed
  6. Repeat for servers B, C, D….and on

Ouch..this was all done manually. This would take way too long to just “check” to see if these jobs failed. To fix this issue I turned to my good friend – powershell. I came up with little script to hit up all my servers, check the SQL jobs, and mail me if a job failed in the last 24 hours.

#Find Failed SQL Jobs with Powershell
#by Adam Mikolaj
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null;
#let's get our list of servers. For this, create a .txt files with all the server names you want to check.
$sqlservers = Get-Content "C:\DevWork\scripts\computers.txt";
#we'll get the long date and toss that in a variable
$datefull = Get-Date
#and shorten it
$today = $datefull.ToShortDateString()
#let's set up the email stuff
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient("ExchangeServerNameHere")
$msg.Body = “Here is a list of failed SQL Jobs for $today (the last 24 hours)”
#here, we will begin with a foreach loop. We'll be checking all servers in the .txt referenced above.
foreach($sqlserver in $sqlservers)
    #here we need to set which server we are going to check in this loop
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;
        #now let's loop through all the jobs
        foreach ($job in $srv.Jobserver.Jobs)
            #now we are going to set up some variables. 
            #These values come from the information in $srv.Jobserver.Jobs
            $jobName = $job.Name;
        	$jobEnabled = $job.IsEnabled;
        	$jobLastRunOutcome = $job.LastRunOutcome;
            $jobLastRun = $job.LastRunDate;
            #we are only concerned about jos that are enabled and have run before. 
            #POSH is weird with nulls so you check by just calling the var
            #if we wanted to check isnull() we would use !$jobLastRun  
            if($jobEnabled = "true" -and $jobLastRun)
                   # we need to find out how many days ago that job ran
                   $datediff = New-TimeSpan $jobLastRun $today 
                   #now we need to take the value of days in $datediff
                   $days = $datediff.days
                       #gotta check to make sure the job ran in the last 24 hours     
                       if($days -le 1 )                    
                            #and make sure the job failed
                            IF($jobLastRunOutcome -eq "Failed")
                                #now we add the job info to our email body. use `n for a new line
                			    $msg.body   = $msg.body + "`n `n FAILED JOB INFO: 
                                 SERVER = $sqlserver 
                                 JOB = $jobName 
                                 LASTRUN = $jobLastRunOutcome
                                 LASTRUNDATE = $jobLastRun"
#once all that loops through and builds our $msg.body, we are read to send
#who is this coming from
$msg.From = ""
#and going to
#and a nice pretty title
$msg.Subject = "FAILED SQL Jobs for $today"
#and BOOM! send that bastard!

Just set this script up to run every morning as a scheduled task and you can enjoy a nice SQL Sandwich instead of plowing through SQL agent jobs.

Next up, I’ll do the same job but use the psx extensions. Wonder which is easier…

One Delete at a Time – Powershell Slice

A co-worker approached me the other day asking for some help with a 3rd party tool. This tool was some sort of auditing software that kept creating trace files and then doing something else with the data.

My co-worker came to me because this tool was sucking up disk space by consistently rolling over 100mb trace files. The tool was supposed to delete theses files based on name, but since they were rolling over too quickly their naming convention was altered.

Normally, they were naming the files something like, “tracefile_1” and then the next should be, “tracefile_2”. Since they were rolling over too quickly they were being named, “tracefile_2-1” and “tracefile_2-2”. The delete job wasn’t setup to eliminate this kind of file. I dug around their code a bit to figure out what it did but instead of going through the whole process of altering and tool, filling out forms, and all that fun stuff, I just wrote a powershell script to delete the oldest file in the folder.

So here is my solution: I set it up to run every hour. It checks the trace file folder and deletes the oldest file one at a time until it reaches under 3gbs.

#put our folder we want to check here
$folder = "c:\temp2"
#now we need to see how big that folder is
$foldersize = (Get-ChildItem $folder | Measure-Object -property length -sum )
#and convert it to GB's
$GBsize = "{0:N2}" -f ($foldersize.sum/ 1GB)
#now, let's check to see if it's over 3 GBs
If ($GBsize -gt 3)
    #if it is, we want to DO the following
        #Let's get the 1st file (sorted by lastwrite time and remove it
        {dir $folder | sort lastwritetime | select -first 1 | remove-item -force
            #now let's recheck the folder size
            $foldersize = (Get-ChildItem $folder | Measure-Object -property length -sum )
            $GBsize = "{0:N2}" -f ($foldersize.sum/ 1GB)
            #print the folder size for testing
        #is the folder less than 3gb? Yes, we are done. No, go back and delete another file
        until ($GBsize -lt 3)
        Write-Host "Deletes Done"
 else {"No deletes Needed"}

Does anyone know a better way to do this?