April 13, 2012 at 1:30 pm
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 🙂
April 16, 2012 at 7:51 am
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()
}
}
}
April 16, 2012 at 8:37 am
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 🙂
April 16, 2012 at 8:59 am
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.
April 16, 2012 at 9:57 am
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