locked
Question on SQLServer Agent Job RRS feed

  • Question

  • Hello,

    I am using SQL Server 2012 SE. I have created an agent job that runs once every 3 minutes. I want to stop this job, disable it and kill its session  and trigger another job if the job takes longer than 10 minutes to complete.

    Is this possible?

    Thanks

    Thursday, September 21, 2017 2:54 PM

Answers

  • That would be the reference for T-SQL and the stored procedures with which you manage SQL Server Agent, possibly also selecting directly from the SQL Server agent system tables.

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sql-server-agent-stored-procedures-transact-sql

    https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/sql-server-agent-tables-transact-sql

    https://docs.microsoft.com/en-us/sql/t-sql/language-reference


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, September 21, 2017 5:16 PM
  • What is it you are actually trying to accomplish with this activity?


    There is nothing built-in to do what you describe.  You would have to write something, like Tibor said.
    Thursday, September 21, 2017 6:29 PM
    Answerer
  • What you describe is doable using T-SQL code. But you'd have to write that T-SQL code and then schedule it as frequently as is suitable for you. I.e., the code will check for how long your job has been executing and then take the actions you describe. Sounds a bit ... backwards to e - I'd fix the root problem if possible. 

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, September 21, 2017 3:22 PM

All replies

  • What you describe is doable using T-SQL code. But you'd have to write that T-SQL code and then schedule it as frequently as is suitable for you. I.e., the code will check for how long your job has been executing and then take the actions you describe. Sounds a bit ... backwards to e - I'd fix the root problem if possible. 

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, September 21, 2017 3:22 PM
  • What you describe is doable using T-SQL code. But you'd have to write that T-SQL code and then schedule it as frequently as is suitable for you. I.e., the code will check for how long your job has been executing and then take the actions you describe. Sounds a bit ... backwards to e - I'd fix the root problem if possible. 


    Thanks for the response. Any references ?
    Thursday, September 21, 2017 5:04 PM
  • Create a new job with query to check if any job runs more than specified time and do the activity(Stop or Kill ..)
    Thursday, September 21, 2017 5:14 PM
  • That would be the reference for T-SQL and the stored procedures with which you manage SQL Server Agent, possibly also selecting directly from the SQL Server agent system tables.

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sql-server-agent-stored-procedures-transact-sql

    https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/sql-server-agent-tables-transact-sql

    https://docs.microsoft.com/en-us/sql/t-sql/language-reference


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, September 21, 2017 5:16 PM
  • What is it you are actually trying to accomplish with this activity?


    There is nothing built-in to do what you describe.  You would have to write something, like Tibor said.
    Thursday, September 21, 2017 6:29 PM
    Answerer