April 17, 2019 at 7:07 pm
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 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.
April 17, 2019 at 8:13 pm
Not sure what is going on, though without more information, this is hard to determine what might be different between the agent and manual execution. Note that with agent, your security context is different.
Also, is this a SQL 2000 issue? If not, which version. This is in an old forum.
April 17, 2019 at 8:38 pm
Hi SSC Guru,
It is SQL Server 2014 Standard Edition. What more information I may provide to get some understanding on the cause of the problem?
Yea, I saw almost similar posting on an old thread but that did not mention that manual execution was working fine or not. So I posted this under Administration with a new topic.
Thanks
April 17, 2019 at 8:53 pm
I posted the topic under Home>Forums>SQL Server 2014>Administration – SQL Server 2014>SQL Server Agent scheduled job taking too long time to run in SQL Server 2014
as this is an old forum which I didn't understand when I first posted. So I request the administrator to delete this post and keep the one in appropriate forum.
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply