Home Dashboard Directory Help
Search

Add an option to set time limits for SQL Agent Jobs by Rpt_User_Al


Status: 

Closed
 as Won't Fix Help for as Won't Fix


1
0
Sign in
to vote
Type: Suggestion
ID: 516456
Opened: 11/30/2009 9:58:21 AM
Access Restriction: Public
0
Workaround(s)
view

Description

In our environment, we know our jobs should run no longer than 30 minutes. I'd like a feature to cause the JOB to complete with a FAILED condition if it runs longer then a certain time. In our case, I'd like to set it to fail if it runs longer than 60 minutes (this allows for a server that is acting sluggish). Our jobs run 24x7.

Here is the thread I posted to discover it is not part of the job definition.
http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/019732bd-f013-4b5b-9798-ab012391b731/

Thanks,
Al Hildreth
Details
Sign in to post a comment.
Posted by Microsoft on 12/11/2009 at 9:01 AM
Thank you for this feedback but we won't be fixing this in our next release. There are several reasons we won't fix.

1. Like Aaron said if we kill anything over a set time - jobs that are 'almost' done would also be stopped. Probably undesirable.
2. Also like Aaron said stopping jobs in this way could cause a roll back that equals the length of the job - also undesirable. Though rollback would only happen for TSQL jobs that utilized transactions. We wouldn't be able to 'rollback' Bat files run via CMDShell etc. Which would cause an inconsistent experience between subsystems.
3. But the biggest reason we won't do this - is that most jobs we can't 'kill' or 'roll back'. When we start a Powershell job we have no way to stopping it or rolling it back, same with CmdShell, Repl, SSIS etc.

Thanks,

Amy Lewis
Posted by Microsoft on 12/11/2009 at 9:01 AM
Thank you for this feedback but we won't be fixing this in our next release. There are several reasons we won't fix.

1. Like Aaron said if we kill anything over a set time - jobs that are 'almost' done would also be stopped. Probably undesirable.
2. Also like Aaron said stopping jobs in this way could cause a roll back that equals the length of the job - also undesirable. Though rollback would only happen for TSQL jobs that utilized transactions. We wouldn't be able to 'rollback' Bat files run via CMDShell etc. Which would cause an inconsistent experience between subsystems.
3. But the biggest reason we won't do this - is that most jobs we can't 'kill' or 'roll back'. When we start a Powershell job we have no way to stopping it or rolling it back, same with CmdShell, Repl, SSIS etc.

Thanks,

Amy Lewis
Posted by Microsoft on 12/11/2009 at 9:01 AM
Thank you for this feedback but we won't be fixing this in our next release. There are several reasons we won't fix.

1. Like Aaron said if we kill anything over a set time - jobs that are 'almost' done would also be stopped. Probably undesirable.
2. Also like Aaron said stopping jobs in this way could cause a roll back that equals the length of the job - also undesirable. Though rollback would only happen for TSQL jobs that utilized transactions. We wouldn't be able to 'rollback' Bat files run via CMDShell etc. Which would cause an inconsistent experience between subsystems.
3. But the biggest reason we won't do this - is that most jobs we can't 'kill' or 'roll back'. When we start a Powershell job we have no way to stopping it or rolling it back, same with CmdShell, Repl, SSIS etc.

Thanks,

Amy Lewis
Posted by Microsoft on 12/11/2009 at 9:01 AM
Thank you for this feedback but we won't be fixing this in our next release. There are several reasons we won't fix.

1. Like Aaron said if we kill anything over a set time - jobs that are 'almost' done would also be stopped. Probably undesirable.
2. Also like Aaron said stopping jobs in this way could cause a roll back that equals the length of the job - also undesirable. Though rollback would only happen for TSQL jobs that utilized transactions. We wouldn't be able to 'rollback' Bat files run via CMDShell etc. Which would cause an inconsistent experience between subsystems.
3. But the biggest reason we won't do this - is that most jobs we can't 'kill' or 'roll back'. When we start a Powershell job we have no way to stopping it or rolling it back, same with CmdShell, Repl, SSIS etc.

Thanks,

Amy Lewis
Posted by Microsoft on 12/11/2009 at 9:01 AM
Thank you for this feedback but we won't be fixing this in our next release. There are several reasons we won't fix.

1. Like Aaron said if we kill anything over a set time - jobs that are 'almost' done would also be stopped. Probably undesirable.
2. Also like Aaron said stopping jobs in this way could cause a roll back that equals the length of the job - also undesirable. Though rollback would only happen for TSQL jobs that utilized transactions. We wouldn't be able to 'rollback' Bat files run via CMDShell etc. Which would cause an inconsistent experience between subsystems.
3. But the biggest reason we won't do this - is that most jobs we can't 'kill' or 'roll back'. When we start a Powershell job we have no way to stopping it or rolling it back, same with CmdShell, Repl, SSIS etc.

Thanks,

Amy Lewis
Posted by Microsoft on 12/11/2009 at 9:01 AM
Thank you for this feedback but we won't be fixing this in our next release. There are several reasons we won't fix.

1. Like Aaron said if we kill anything over a set time - jobs that are 'almost' done would also be stopped. Probably undesirable.
2. Also like Aaron said stopping jobs in this way could cause a roll back that equals the length of the job - also undesirable. Though rollback would only happen for TSQL jobs that utilized transactions. We wouldn't be able to 'rollback' Bat files run via CMDShell etc. Which would cause an inconsistent experience between subsystems.
3. But the biggest reason we won't do this - is that most jobs we can't 'kill' or 'roll back'. When we start a Powershell job we have no way to stopping it or rolling it back, same with CmdShell, Repl, SSIS etc.

Thanks,

Amy Lewis
Posted by Microsoft on 12/11/2009 at 9:01 AM
Thank you for this feedback but we won't be fixing this in our next release. There are several reasons we won't fix.

1. Like Aaron said if we kill anything over a set time - jobs that are 'almost' done would also be stopped. Probably undesirable.
2. Also like Aaron said stopping jobs in this way could cause a roll back that equals the length of the job - also undesirable. Though rollback would only happen for TSQL jobs that utilized transactions. We wouldn't be able to 'rollback' Bat files run via CMDShell etc. Which would cause an inconsistent experience between subsystems.
3. But the biggest reason we won't do this - is that most jobs we can't 'kill' or 'roll back'. When we start a Powershell job we have no way to stopping it or rolling it back, same with CmdShell, Repl, SSIS etc.

Thanks,

Amy Lewis
Posted by AaronBertrand on 11/30/2009 at 3:52 PM
This could be problematic, as in a lot of cases, it would have to issue a ROLLBACK. What if at 30 minutes it was 2 seconds away from completion, now a ROLLBACK could take an additional 30 minutes or more? I propose a different approach: have a monitor job that checks the status of currently executing jobs. If any in your target range have been running more than 30 minutes, it lets them continue running, but sends an alert and/or e-mail so that you can check it out manually. This way you can let the job finish if you discover that there are external factors causing the delay, or you can stop it yourself, instead of letting the system blindly perform a hard stop.
Sign in to post a workaround.