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.