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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy