April 17, 2019 at 8:50 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 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.
April 17, 2019 at 10:18 pm
Cross post of https://www.sqlservercentral.com/forums/topic/sql-server-agent-scheduled-job-taking-abnormally-long-time-to-run-2
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 18, 2019 at 12:32 am
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.
April 18, 2019 at 5:07 am
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
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply