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. 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. 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. It was necessary as the drive had only about 100 GB of free space out of 1.8 TB. Then I ran shrink file to recover the freed space. I created proper cluster index before populating the data in the tables. 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.
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.
Needless to say it didn’t run in subsequent hours. 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. 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. However the same job when I right click on the job and select “Start job at step…”, it finishes at desired time.
I am having a quite strange problem- the scheduled job in SQL Server Agent is taking abnormally long time to run.
0 kommentar(er)
