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…

Bring it on 2012

Well, I’m about a week and a half late with the whole New Year blog post but I figure better late than never.

So just a little recap of my year. 2011 was the first year I started blogging about SQL. It was pretty rewarding and I hope to do more of it this year. When I got in the zone things just flowed but when work or other life distractions got in the way things slowed down a bit.

Last year I was able to learn a good bit about:

  • Powershell, Powershell, Powershell – I am still at the beginning of my journey but some of the things I have done so far have saved me so much time.
  • Access (unfortunately) – some critical systems got tossed my way that were dependant on access. I had to learn how to deal with them. It wasn’t fun but it was awesome once everything worked (for that week).
  • Big DBs – This year I got to work with some monsters. It’s been a good oppurtunity so far. It makes tuning even more fun.
  • Indexes – used, needed, overlapping, and of course…fragmented.
  • DBCC commands – Just when you think you know some of these, you learn a new one that totally changes how you work.
  • Twitter – this is the first I have ever used twitter. I used to think it was the stupidest thing in the world. I started using it a bit and holy crap, a whole new world opened up. I can’t believe the people I have interacted with on there. SQL has a great community and I hope I can give back a little more this year.
  • Double Hoping and SPNs – every DBA needs to go through this at least once.
  • Extended Events – Profiler? Who needs that?

Overall it’s been another great year to be a DBA. I like what I do and just want to get better and increase the awesome meter.

This year I’ll be focusing a lot on powershell, puffy clouds of computers, 2012 of course, extended events, and probably get another cert or 3. I WILL also present 1 or 2 times this year. Something I have never done before. If you got advice, send it my way.

The other area I am hoping to dip more into is BI. I have a feeling that there will be a big industry swing once 2012 comes out for really good BI people. I’m hoping to jump on that wagon.

I will also be moving to the states in the near future. That is going to be a whole new world of adventure that I am ready for.

So 2012, bring it.

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?

You want to start down the powershell road?


Yup, you’ve heard of it. You want to use it because everyone is talking about it. They say, “It’s soooooo awesoooommmmmeeee man, you gotta powershell it up!”

I also jumped on that ship and started into the powershell adventure.

Before we get into any powershell fun, we need to figure out where to go to use powershell.

Even before that, we need to figure out if we even HAVE powershell:

  • Go to Run and type in powershell, if you have it, a new powershell window will pop up.

If  you don’t have powershell, you should get powershell v2. GOTTA get that v2.

You have two options if you want to do some powershelling. You can use Windows Powershell or you can use Windows Powershell Integrated Scripting Environment (ISE).

I recommend using the ISE. It’s makes life 1000x easier.

If you want to run scripts from regular powershell, it’s not as easy as it seems.

Here’s how:

Step one:

  • Are you even allowed to run powershell? Probably not off the bat.

The FIRST thing you need to do is check if you can run scripts or not.
To do this type in Get-ExecutionPolicy

Now, if you haven’t run powershell before it’s probably set to restricted.

To change this you need to run this command:
Set-ExecutionPolicy (whatever option you choose)

What are your options?

  • Restricted – Can’t do jack. No scripts for you.
  • RemoteSigned – all downloaded scripts have to be signed but yours are fine
  • Allsigned – Only scripts that are signed and trusted can be run.
  • Unrestricted -FREEDOM! You can run any untrusted script.

Once you figure out which execution policy you want, you can run scripts.

Your other option here is go use the ISE. The ISE is very user friendly. You can type out your whole scrip, hit run, and bammajamma = scriptness!

“Do I even have the ISE?” Well, go to your windows search and type in ISE. If nothing shows up, you probably don’t have it installed.

It’s super easy to install. Just open up a new powershell window and type in: Import-Module ServerManager; Add-WindowsFeature PowerShell-ISE

You should get a nice pretty:


Now you are ready to go! So…..powershell away. More to come in my adventures in powershell.