April 17, 2019 at 6:59 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 when I dropped some big tables and then run shrink file. It was necessary as HDD capacity decreased to about 100 GB (total capacity of the drive was 1.8 TB). After shrink the files about 1 TB was freed. I did the re-indexing as well after shrink. My biggest question is that why is it taking too long when it is triggered by the SQL Server Agent scheduler. If I run it manually from the Job Agent, or SSIS or even the individual scripts of the job there is no issue. If it was due to coding or index or anything like that it would have experienced the same slowness in every case.
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:10 pm
Closing as this looks like a dupe
Viewing 2 posts - 1 through 1 (of 1 total)
The topic ‘SQL Server Agent scheduled job taking abnormally long time to run’ is closed to new replies.