August 29, 2015 at 10:25 pm
Hi,
I have a procedure which is scheduled as sql job and runs once in every day. The procedure has transactional logic which deals with millions of records. As this is dealing with large data, will sqlserver tends to timeout? My learning is , sqlserver doesn't have timeout and the sqljob runs in server at any cost it will not trigger timeout exception. Please correct if my understanding is correct? or does sql job may get timeout? Which is true.
Please advice me.
August 30, 2015 at 12:09 am
the query can timeout if it is unable to acquire the required locks and resources. Every application needs timeot to prevent a query from running indefinelty. The Job might run for as long as needed but the queries within the job can still timeout if sql sees it as idle ( doing nothing)( if the query is actually performing work then it can continue to run for hours but idle state is acceptable only for few minutes.
try changing the logic to perform operations in batchs. Check where the timeout occure , tempdb , lock , block or deadclock? see if the right indexes are in place
August 30, 2015 at 7:40 am
Hello Jayanth,
thanks for the replay and i am not sure what do you meant by idle. Lets proceed with example, i have to deal with 500*1000 records using the sql job(using procedure) and if one of the tables used inside the sql query doesn't have proper index and because of it takes time to process the data for an hour(assumption). will the timeout issue occur on this situation?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply