Checking SQL Server Agent jobs using Windows PowerShell

By:   |   Comments (11)   |   Related: > PowerShell


Problem

Checking for SQL Server Agent jobs and their status is part of your daily task as a DBA. How do we use Windows PowerShell to check for SQL Server Agent jobs?

Solution

Similar to the task described in the tip Check the Last SQL Server Backup Date using Windows PowerShell, it would require reading the tables in the msdb database and joining them appropriately to find out which jobs failed. Two tables in particular are of interest to us to check for job execution information like job name, execution status, run date, run time, etc. - the sysjobs and sysjobhistory tables. 

The script below displays a list of jobs on your SQL Server instance with there status.

USE msdb  
GO  
SELECT  
   j.[name] AS [JobName],  
   run_status = CASE h.run_status  
   WHEN 0 THEN 'Failed' 
   WHEN 1 THEN 'Succeeded' 
   WHEN 2 THEN 'Retry' 
   WHEN 3 THEN 'Canceled' 
   WHEN 4 THEN 'In progress' 
   END, 
   h.run_date AS LastRunDate,   
   h.run_time AS LastRunTime 
FROM sysjobhistory h  
   INNER JOIN sysjobs j ON h.job_id = j.job_id  
       WHERE j.enabled = 1   
       AND h.instance_id IN  
       (SELECT MAX(h.instance_id)  
           FROM sysjobhistory h GROUP BY (h.job_id))
GO 
job name

Notice that the run_date and run_time columns of the sysjobhistory table are of type int and would be a bit challenging to convert the columns to their appropriate data types.  Server Management Objects (SMO) exposes these properties when using Windows PowerShell. The JobServer property of the Server object represents the SQL Server Agent associated with an instance of SQL Server. This includes the SQL Server jobs, operators and alerts.

When translating the T-SQL query above to Windows PowerShell, we would be interested in the Name, LastRunDate and LastRunOutcome properties of the Jobs object. What's really good to note is that the LastRunDate property is in a datetime format that no longer requires conversion to the appropriate data type, similar to what we get from the sysjobhistory table in the msdb database.  I keep trying to highlight this for every tip I've written that uses PowerShell with SMO.  The only aspect of the code that we have changed from the scripts in the previous tips is the last line, i.e. adding new properties for the new objects we are working with. This highlights the power and simplicity of Windows PowerShell from the script.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$rvs = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2000" 

#Create an instance of the Jobs object collection from the JobServer property
#And pipes that to the filter Where-Object cmdlet to retrieve only those jobs that are enabled but failed 
$srv.JobServer.Jobs | Where-Object {$_.IsEnabled -eq $TRUE} | Select Name,LastRunOutcome, LastRunDate
system

Notice that the LastRunDate property is in the correct data type. The LastRunOutcome property is returned as they are without the need for further translations as in the T-SQL script above. Let's call to the Excel object as we did in the previous tips to format the results. Again, the script above is more than enough for what we need.

#Create a new Excel object using COM 
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

#Counter variable for rows
$intRow = 1
#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "D:\SQL_Servers.txt")
{
     #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True
     $intRow++
      $Sheet.Cells.Item($intRow,1) = "JOB NAME"
      $Sheet.Cells.Item($intRow,2) = "LAST RUN OUTCOME"
      $Sheet.Cells.Item($intRow,3) = "LAST RUN DATE"
     #Format the column headers
     for ($col = 1; $col -le 3; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }
     $intRow++
      #######################################################
     #This script gets SQL Server Agent job status information using PowerShell
     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
     # Create an SMO connection to the instance
     $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
     $jobs=$srv.JobServer.Jobs
     #Formatting using Excel 

ForEach ($job in $jobs)  
{ 
       # Formatting for the failed jobs 
       if ($job.LastRunOutcome -eq 0) 
       { 
           $fgColor = 3 
       } 
       else 
       { 
           $fgColor = 0 
       } 
    
       $Sheet.Cells.Item($intRow, 1) =  $job.Name 
       $Sheet.Cells.Item($intRow, 2) = $job.LastRunOutcome.ToString() 
       $Sheet.Cells.item($intRow, 2).Interior.ColorIndex = $fgColor 
       $Sheet.Cells.Item($intRow, 3) =  $job.LastRunDate
            
       $intRow ++ 
} 
   $intRow ++ 
}
$Sheet.UsedRange.EntireColumn.AutoFit()
cls
instance name
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Wednesday, June 26, 2024 - 5:16:06 AM - Sushil Kumar Back To Top (92344)
I get only get below while running below there is no Job information , can someone help me out ?
JOB NAME IS THE JOB ENABLED SCHEDULE SUB-DAY SCHEDULE


#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True

$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

#Counter variable for rows
$intRow = 1

#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "D:\SQL_Servers.txt")
{

#Create column headers
$Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
$Sheet.Cells.Item($intRow,2) = $instance
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2).Font.Bold = $True

$intRow++

$Sheet.Cells.Item($intRow,1) = "JOB NAME"
$Sheet.Cells.Item($intRow,2) = "IS THE JOB ENABLED"
$Sheet.Cells.Item($intRow,3) = "SCHEDULE"
$Sheet.Cells.Item($intRow,4) = "SUB-DAY SCHEDULE"



#Format the column headers
for ($col = 1; $col –le 4; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 49
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 2
}


$intRow++
#######################################################
#This script gets SQL Server Agent job status information using PowerShell


[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# Create an SMO connection to the instance
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

$jobs=$srv.JobServer.Jobs

#Formatting using Excel


ForEach ($job in $jobs)
{

$Sheet.Cells.Item($intRow, 1) = $job.Name
$Sheet.Cells.Item($intRow, 2) = $job.IsEnabled

ForEach ($Schedule in $job.JobSchedules)
{

$Sheet.Cells.Item($intRow, 3) = $schedule.FrequencySubDayInterval
$Sheet.Cells.Item($intRow, 4) = $schedule.FrequencySubDayInterval


}



$intRow ++

}
$intRow ++


}

$Sheet.UsedRange.EntireColumn.AutoFit()
cls

Monday, July 21, 2014 - 9:47:01 AM - ariel Back To Top (32802)

HA!  I made a typo correcting your typo!!!  sorry.  Delete my last one!  Cheers!

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$rvs = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2000"

$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2000"

 

#Create an instance of the Jobs object collection from the JobServer property

#And pipes that to the filter Where-Object cmdlet to retrieve only those jobs that are enabled but failed

$srv.JobServer.Jobs | Where-Object {$_.IsEnabled -eq $TRUE} | Select Name,LastRunOutcome, LastRunDate


Friday, October 25, 2013 - 1:56:58 PM - Mersild Back To Top (27287)

I think I must be missing something because I am a n00b at powershell.  The Excel SpreadSheet is gets created and the servernames are added but the value data is missing.  Can you please advise me why I get the following errors:

PS C:\scripts> .\SQLJobsReport.ps1
New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: make sure the assembly containing this type
is loaded.
At C:\scripts\SQLJobsReport.ps1:33 char:18
+ $srv = New-Object <<<<  ('Microsoft.SqlServer.Management.Smo.Server') $instance
    + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: make sure the assembly containing this type
is loaded.
At C:\scripts\SQLJobsReport.ps1:141 char:23
+      $srv = New-Object <<<<  ('Microsoft.SqlServer.Management.Smo.Server') $instance
    + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

You cannot call a method on a null-valued expression.
At C:\scripts\SQLJobsReport.ps1:185 char:68
+        $Sheet.Cells.Item($intRow, 2) = $job.LastRunOutcome.ToString <<<< ()
    + CategoryInfo          : InvalidOperation: (ToString:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNulle


Tuesday, September 24, 2013 - 2:41:53 PM - bass_player Back To Top (26919)

This is a great inclusion in the script. Thanks, Prashanth


Tuesday, September 24, 2013 - 11:53:08 AM - Prashanth T Back To Top (26916)

A very helpful script. I have added couple of steps to include the job schedule as well. 

 

#Create a new Excel object using COM 

$Excel = New-Object -ComObject Excel.Application

$Excel.visible = $True

 

$Excel = $Excel.Workbooks.Add()

$Sheet = $Excel.Worksheets.Item(1)

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

 

 

#Counter variable for rows

$intRow = 1

 

#Read thru the contents of the SQL_Servers.txt file

foreach ($instance in get-content "C:\SQLServers_List.txt")

{

 

$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

 

     #Create column headers

    $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"

     $Sheet.Cells.Item($intRow,2) = "COLLATION"

$Sheet.Cells.Item($intRow,3) = "VERSION:"

     $Sheet.Cells.Item($intRow,4) = "EDITION"

$Sheet.Cells.Item($intRow,5) = "IS ClUSTERED"

     

#$Sheet.Cells.Item($intRow,5) = "BACKUP LOCATION TYPE"

     #$Sheet.Cells.Item($intRow,6) = "BACKUP LOCATION"

 

   

    $Sheet.Cells.Item($intRow,1).Font.Bold = $True

     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

$Sheet.Cells.Item($intRow,3).Font.Bold = $True

$Sheet.Cells.Item($intRow,4).Font.Bold = $True

$Sheet.Cells.Item($intRow,5).Font.Bold = $True

 

 

     $intRow++

 

 $Sheet.Cells.Item($intRow,1) = $instance

$Sheet.Cells.Item($intRow,2) = $srv.Collation

$Sheet.Cells.Item($intRow,3) = $srv.Version

$Sheet.Cells.Item($intRow,4) = $srv.EDITION

$Sheet.Cells.Item($intRow,5) = $srv.ISClustered

 

 

$intRow++

 

      $Sheet.Cells.Item($intRow,1) = "JOB NAME"

      $Sheet.Cells.Item($intRow,2) = "LAST RUN OUTCOME"

      $Sheet.Cells.Item($intRow,3) = "LAST RUN DATE"

 $Sheet.Cells.Item($intRow,4) = "NEXT RUN DATE"

 $Sheet.Cells.Item($intRow,5) = "FrequencyTypes"

      $Sheet.Cells.Item($intRow,6) = "FrequencySubDayTypes"

 $Sheet.Cells.Item($intRow,7) = "Enabled"

 

     #Format the column headers

     for ($col = 1; $col –le 8; $col++)

     {

          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True

          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48

          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34

     }

 

     $intRow++

      #######################################################

     #This script gets SQL Server Agent job status information using PowerShell

 

     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

 

     # Create an SMO connection to the instance

     $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

 

     $jobs=$srv.JobServer.Jobs | Where-Object {$_.Name -like "*Backup*"}

 

     #Formatting using Excel

 

 

ForEach ($job in $jobs)  

 

       # Formatting for the failed jobs 

       if ($job.LastRunOutcome -eq 0) 

       { 

           $fgColor = 3 

       } 

       else 

       { 

           $fgColor = 0 

       } 

   

 

       $Sheet.Cells.Item($intRow, 1) =  $job.Name 

       $Sheet.Cells.Item($intRow, 2) = $job.LastRunOutcome.ToString() 

       $Sheet.Cells.item($intRow, 2).Interior.ColorIndex = $fgColor 

       $Sheet.Cells.Item($intRow, 3) =  $job.LastRunDate

  $Sheet.Cells.Item($intRow, 4) =  $job.NextRunDate

  $Sheet.Cells.Item($intRow, 7) = $job.IsEnabled

   ForEach ($Schedule in $job.JobSchedules)  

 

 

       If ( $Schedule.FrequencyTypes -eq 4 )

  { 

  $Sheet.Cells.Item($intRow, 5) =  "Daily"

  }

  elseif ( $Schedule.FrequencyTypes -eq 8) 

  {

  $Sheet.Cells.Item($intRow, 5) =  "Weekly"

  $Sheet.Cells.Item($intRow, 6) =  "Check Job for which day & time"

  }

  elseif ( $Schedule.FrequencyTypes -eq 16) 

  {

  $Sheet.Cells.Item($intRow, 5) =  "Monthly"

  $Sheet.Cells.Item($intRow, 6) = "Every " + $Schedule.Frequencyrecurrencefactor +" Months" 

  }

   elseif ( $Schedule.FrequencyTypes -eq 32) 

  {

  $Sheet.Cells.Item($intRow, 5) =  "Monthly"

  $Sheet.Cells.Item($intRow, 6) = "Every " + $Schedule.Frequencyrecurrencefactor +" Months"

  } 

If ( $Schedule.FrequencySubDayTypes -eq 1 )

  { 

  $Sheet.Cells.Item($intRow, 6) =  "Once"

  }

  elseif ( $Schedule.FrequencySubDayTypes -eq 8) 

  {

  $Sheet.Cells.Item($intRow, 6) =  "Every " + $Schedule.FrequencySubDayInterval +" Hours"

  }

  elseif ( $Schedule.FrequencySubDayTypes -eq 2) 

  {

  $Sheet.Cells.Item($intRow, 6) =  "Every " + $Schedule.FrequencySubDayInterval +" Seconds"

  }

 

  elseif ( $Schedule.FrequencySubDayTypes -eq 4) 

  {

  $Sheet.Cells.Item($intRow, 6) =  "Every " + $Schedule.FrequencySubDayInterval + " Minutes"

  }

 

 

 

}

    

            

       $intRow ++ 

   

   $intRow ++ 

 

 

}

 

$Sheet.UsedRange.EntireColumn.AutoFit()

 

cls

 
 

Wednesday, May 8, 2013 - 1:01:39 AM - Kalebb Back To Top (23786)

Hi!

I managed to make it work this way. I guess I only have to translate it to a more "human" language since it only shows numbers,

It is done with an IF, isn't it?

Thanks in advance, this article helped me so much!!

#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True

$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

#Counter variable for rows
$intRow = 1

#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "C:\SQL_Servers.txt")
{

     #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "JOB NAME"
      $Sheet.Cells.Item($intRow,2) = "IS THE JOB ENABLED"
      $Sheet.Cells.Item($intRow,3) = "SCHEDULE"
      $Sheet.Cells.Item($intRow,4) = "SUB-DAY SCHEDULE"

 

     #Format the column headers
     for ($col = 1; $col –le 4; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 49
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 2
     }


     $intRow++
      #######################################################
     #This script gets SQL Server Agent job status information using PowerShell


     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

     # Create an SMO connection to the instance
     $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

     $jobs=$srv.JobServer.Jobs

     #Formatting using Excel


ForEach ($job in $jobs) 

       $Sheet.Cells.Item($intRow, 1) =  $job.Name
       $Sheet.Cells.Item($intRow, 2) = $job.IsEnabled
               
               ForEach ($Schedule in $job.JobSchedules) 

           $Sheet.Cells.Item($intRow, 3) = $schedule.FrequencySubDayInterval
           $Sheet.Cells.Item($intRow, 4) = $schedule.FrequencySubDayInterval
   
          
}

   
           
       $intRow ++
  
}
   $intRow ++


}

$Sheet.UsedRange.EntireColumn.AutoFit()
cls

 


Tuesday, May 7, 2013 - 4:20:23 PM - bass_player Back To Top (23778)

Can you post your PowerShell code?


Tuesday, April 30, 2013 - 5:49:53 PM - Kalebb Back To Top (23645)

Hi Again!

I´m triying to display SubDayFrequency, but I get a Null Value Error. I see that it is on a Different class, so how would it be the right way to call/display that? Should I include another FOR EACH with a different variable or can I use the same?

Thanks in advance,

Regards.


Monday, April 29, 2013 - 6:24:56 PM - Kalebb Back To Top (23624)

Thank you so much! You really saved my life! This article is very clear and very well explained. Thanks again!


Monday, April 29, 2013 - 10:49:24 AM - bass_player Back To Top (23616)

Kalebb,

You can explore the object properties, methods and events for any SMO object by using the Get-Member cmdlet. This will expose all of the object characteristics for you to explore even better. To identify which SQL Server Agent jobs are enabled or not, use the IsEnabled property of the Job class

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.job.aspx


Sunday, April 28, 2013 - 7:36:31 PM - Kalebb Back To Top (23601)

Hi!

This is awesome!! This script works great and allows me to make a full Jobs-Catalog of all my instances, but How can I change it so I can know which jobs are enabled, its Schedule and Daily Frequency? I used to do this with an SQL Script, but this way is easier!

Any help will be really appreciated since I am completely new to Powershell (And I'm really loving it).

 

Regards!















get free sql tips
agree to terms