Post reply

Adding a time limit to a SQL Job

  • January 11, 2013 at 5:29 am

    #287710

    Hi all,

    I was wondering if it was possible to add a time limit to a SQL Job step so that it will automatically fail the step and move onto the next one if that limit is reached?

    Thanks,

    Matt

  • January 11, 2013 at 5:40 am

    #1576220

    my outside of the box comment:

    wouldn't it be better to tune the process that seems slow so that it performs better?

    what is that process doing, and does it involve a cursor?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 11, 2013 at 5:50 am

    #1576229

    I wholeheartedly agree....however unfortunately that is out of my hands! The job actually initiates backups using Netbackup, however when we do our weekly backups the job frequently runs for more than 24 hours because the SQL Backups get queued along with all our other data and file backups etc. This means that when the job tries to run the next night it won't be able to because it's already running. There is a second step in the job that fires off some normal maintenance plan backups to disk, so as belt and braces I would like to make sure this can run every night regardless of whether the Netbackup job has finished - hence why I would like to cancel that step after a period of time if needed (as both jobs can't be run simultaneously without locking each other).

    Our server team is looking into improving the performance of the backups but for now I am looking for a temporary solution!

  • January 11, 2013 at 6:53 am

    #1576260

    The only way I know how to do this would be to modify the timeout for the connection. You could enable the query governor to limit long running queries and you can set the server level remote timeout. But to really limit a single query, I'd go after the connection settings.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 11, 2013 at 6:53 am

    #1576261

    What kind of job step is it? If it's T-SQL and you want a timeout less than ~18 hours you could call the batch using sqlcmd.exe and provide the -t option to time the batch out at a certain point.

    sqlcmd Utility

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • January 11, 2013 at 7:06 am

    #1576270

    Thanks guys - the job step type is actually Operating System (CmdExec)...not sure if there may be a switch I can add to that command to make it timeout? I will look into the connection timeout too....cheers 🙂

  • January 11, 2013 at 7:36 am

    #1576299

    hey Matt.gyton,

    Take a look at this topic i asked kind of the same thing.

    http://www.sqlservercentral.com/Forums/Topic1404744-391-1.aspx

    I haven't tested it yet.

    Marbo

  • January 11, 2013 at 8:35 am

    #1576348

    Thanks Marbo - that looks like it might work!

  • January 11, 2013 at 8:52 am

    #1576367

    matt.gyton (1/11/2013)

    Thanks guys - the job step type is actually Operating System (CmdExec)...

    PowerShell support async jobs, that you can then check on and stop if they run too long. PowerShell pseudocode:

    1. Call Start-Job to start the backup executable.

    2. Call Wait-Job with the desired -Timeout setting.

    3. When Wait-Job returns control check the status of the job. If it has completed successfully, great, all done. If not then report the error or timeout and allow the next job step to run.

    No messy polling logic in T-SQL checking the msdb job tables.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • January 11, 2013 at 11:22 am

    #1576460

    When the potential-trouble step starts, it could immediately start another job. A job start is async so it would not delay the first process. The extra job could WAITFOR the desired amount of time, then check the first job: if it is still running, it could stop the job, then maybe restart it at the next step (? not sure about that last one, but I think it can be done).

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • January 11, 2013 at 4:27 pm

    #1576529

    matt.gyton (1/11/2013)

    Thanks guys - the job step type is actually Operating System (CmdExec)...not sure if there may be a switch I can add to that command to make it timeout? I will look into the connection timeout too....cheers 🙂

    Oh, no.... you don't want such a thing to timeout, quit, fail, or be killed because it won't necessarily kill the SPID using the CmdExec. It will sometimes sit in a "zero rollback" forever after doing nothing but burning clock cycles as fast as it can.

    I don't know what else you can do but I wouldn't be doing THAT!

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 12, 2013 at 9:24 am

    #1576603

    Personally I would encourage you to focus on the big picture here and that is that your current backup technology solution is not meeting your business requirements.

    What is causing your netbackup process to take so long? I've used NetBackup in several shops and have not encountered such excessive durations, even when hosting 100s of multi-terabyte database environments.

    You're either backing up too much data (possibly a combination of an unsuitable SQL Server Backup Strategy and/or NetBackup over lay solution) or your infrastructure is not up to the task being asked of it.

    I get that you're trying to make the best of a difficult situation but the focus/pressure needs to be on other areas of the business to get it together. Somewhat counter-intuitively it's not in the best interest of your SQL Server assets to come up with a SQL Server managed "solution".

    As an aside, I would also consider looking to move the management/scheduling of your NetBackups away from SQL Server.

    John Sansom (@sqlBrit) | www.johnsansom.com

Viewing 12 posts - 1 through 11 (of 11 total)

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