Need help with script - powershell and sql agent job

  • Hi All,

    I am writing this script to check if the sql agent job is running or not and if it is not running disable it.

    Some how I am not able to disable it and it is not evaluating the if loop. I am missing some stuff here 😀

    also, how can I make the script to take a user input and enable or disable the job.

    $srv = New-Object Microsoft.SqlServer.Management.SMO.Server("server\instance")

    $jobs = $srv.jobserver.jobs

    #$jobs | where-object {$_.Isenabled -eq $true -and $_.name -like "*backup*"}

    if ($_.IsEnabled -eq $True -and $_.name -match "*backup*")

    {

    $JobsExecuting = 0

    foreach ($job in $jobs)

    {#if the job is running

    if ($job.CurrentRunStatus.ToString() -ne "Idle")

    {

    write-host "you hit inner loop"

    $jobExecuting = 1

    write-host "The backup job is running ..."

    break

    }

    }

    if($JobsExecuting -eq 0)

    {

    #if the job is not executing

    write-host "no jobs are running on $($srv)"

    #disable the job

    $jobs.IsEnabled = $TRUE

    $jobs.Alter()

    }

    }

    else

    {

    write-host "$($srv.name) has running jobs, try again later" -foregroundcolor Red

    }

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • You just missed a couple of small things but they do make a difference.

    First -like and -match work differently

    Second, in your line $jobs.IsEnabled = $TRUE

    $jobs is a collection of the jobs and doesn’t have a IsEnabled property.

    Also $TRUE enables the job, use $false to disable it.

    Here’s an example. You can call it with the –disable argument to disable the jobs or leave that off to just show the current status.

    param ( [switch]$disable )

    $srv = New-Object Microsoft.SqlServer.Management.SMO.Server("server\instance")

    $jobs = $srv.jobserver.jobs | where-object {$_.Isenabled -eq $true -and $_.name -like "*backup*"}

    foreach ($job in $jobs){

    #if the job is running

    if ($job.CurrentRunStatus.ToString() -ne "Idle") {

    write-host "you hit inner loop"

    write-host "The backup job is running ..."

    write-host "$($srv.name) $($job.name) is running, try again later" -foregroundcolor Red

    }

    else {

    #if the job is not executing

    write-host "$($job.name) is not running on $($srv.Name)"

    if ($disable) {

    #disable the job

    $job.IsEnabled = $FALSE

    $job.Alter()

    }

    }

    }

  • Thanks .. that helped.

    how can I make the script to take a user input and enable or disable the job ?

    Do I have to use -- read-host for any user input ?

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • No need to read-host. I added that "param ( [switch]$disable )" line so you can add -disable (or not) to the command line when calling your script.

    For example, if you name the script disableJob.ps1 then calling the job like this

    PS:> .\disableJob -disable

    it will disable jobs matching your criteria (not running and with names like *backup*).

    If you just execute the job without that -disable switch then it won't actually disable anything. It will just display your write-host lines.

  • Thanks .. got it !

    I was struggling with http://www.sqlservercentral.com/Forums/Topic1282812-1351-1.aspx and did not find any solution.

    Is it possible for you to point me in right direction.

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply