SQL Server Agent scheduled job taking too long time to run in SQL Server 2014

  • ajoy_gp

    Old Hand

    Points: 334

    I am having a quite strange problem- the scheduled job in SQL Server Agent is taking abnormally long time to run. However the same job when I right click on the job and select “Start job at step…”, it finishes at desired time. Usually the job should be finished in about 15 to 17 minutes (it works on pretty big data set having about 19 joins and each table has more than 15 million rows. The source tables are all properly clustered indexed) but when it ran at the scheduled time it took whopping 13 hours !!! The job is scheduled to run every hour. Needless to say it didn’t run in subsequent hours. After that abnormal timing it took 25 minutes (still about double time than normal) in the next scheduled hour and then again fall in the same situation on the next hour.

    The problem started after I dropped about 15 very big tables each with more than 100 million rows of data and then created the same tables with much smaller (about 15 million rows each) size. I created proper cluster index before populating the data in the tables. Then I ran shrink file to recover the freed space. It was necessary as the drive had only about 100 GB of free space out of 1.8 TB. After shrinking it gained about 1 TB.

    My biggest question is why would it take so long when triggered by SQL Server Agent scheduler but running fine when manually run from the SQL Server Agent or SSIS or each individual script of the job. If there was any issue with poorly written code or bad index, it would have created same problem in every method as per my understanding.

    I couldn’t find any problem at all. To give a try I deleted the job and also the SSIS package and then recreated the package with a different name and scheduled the job. First ran it manually and it finished within 15 minutes but on the scheduled hour it faced the same abnormal situation again. It started about 50 minutes ago and still running and most probably I will kill the process and run again manually. I will search web for solution and in the mean while any good answer is highly appreciated.

    By mistake I posted this in SQL Server 7,2000 forum but now brought to the correct forum. This is SQL Server 2014 Standard edition.

  • Thom A

    SSC Guru

    Points: 98719

    Cross post of https://www.sqlservercentral.com/forums/topic/sql-server-agent-scheduled-job-taking-abnormally-long-time-to-run-2


    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • ajoy_gp

    Old Hand

    Points: 334

    Yes, this is a cross post as the previous one was posted in forum of SQL Server 2000 and older versions which the editor Steve Jones mentioned. So I requested the moderator to delete that post and I created the same topic here.

    Update on the situation- after couple of manual run and killing the job that was initiated by the SQL Server Agent scheduler it is running fine (finishing in 11 to 13 minutes) for last 3 hours. I haven't done anything though. So the problem is gone for the time being. Still any reasonable explanation behind the previous behavior is highly appreciated.


  • Jeff Moden

    SSC Guru

    Points: 997123

    If you did a shrink-file, you inverted some index and that's the worst form of fragmentation there is.  You MUST do index maintenance to any fragmented index immediately after a shrink file.


    As for why you running it manually compared to through the agent is so different, "It Depends".  There's a pretty good chance that the two methods are slightly different and that one or the other is still stuck on the old execution plan... probably the agent jobs.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

Viewing 4 posts - 1 through 4 (of 4 total)

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