I have these job running early morning everyday and at least once a week the job fails with the following error message:
My question is how can I find the other process/job that is chosen over these job by sql server? what should I do to prevent SQL Server choosing these job as a deadlock victim?
Did you have the traceflags set? In SQL Server 2000, traceflag 1204, in 2005/2008, traceflag 1222. These will capture deadlock information into the error log when a deadlock occurs. I prefer them to using a server side trace because they're things you set once and then don't have to worry about managing afterwards.
answered Mar 14 '10 at 10:28 PM
Grant Fritchey ♦♦
Run Profiler and set up a trace to collect information about what's going on, including the Deadlock Graph. This will give you what you need.
And if you prefer, you can make it a server-side trace so that you don't need to have Profiler running for very long.
answered Mar 14 '10 at 07:53 PM