April 13, 2012 at 3:33 am
Hi,
I have set the data collection for one of our server. But two jobs (collection_set_2_upload and collection_set_3_upload) get failed sometimes because of deadlock. The job history gives the below message.
" Transaction (Process ID 136) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed."
These jobs are created as part of the data collection and MSDB tables are used for this. Has anybody faced this issue before for data collection jobs? Isnt this a bug?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 13, 2012 at 4:33 am
I don't use data collection, but if these are two separate jobs, shouldn't you be able to schedule them so they run one after the other instead of at the same time?
April 13, 2012 at 5:11 am
No, if I change the schedule of one job, other automatically gets changed. Both runs at the same time.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 13, 2012 at 5:25 am
That's because they're using the same schedule (you can have jobs use an existing schedule, or each job have their own brand new schedule). Delete the schedule from job 3 and create a new one that schedules it at a slightly later time.
I don't know what that will do to the data collection, but that should prevent the jobs from deadlocking each other. I am assuming that the deadlock issue comes from one of these two deadlocking the other, not a third process deadlocking these two jobs.
If the later, then you need to reschedule both job so they don't run while the third process runs.
April 13, 2012 at 5:56 am
I can do that and see if it makes any difference. Thanks.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply