SQL Server Deadlock Problem - How to Resolve ?

Hi All

We have a SQL Job (2005) that from time to time will fail due a deadlock. The error is as follows ;

Transaction (Process ID 52) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.

This is an overnight job which effectively loads a reporting database and performs various corrections to the data. There are no other DML statements running simultaneously. The error occurs about once a week on differing days and at different points in the data load process. So I am presuming it is not due to DML statements.

Turning on Trace Flag 1222 provides information that is next to useless (log records below). Most of the articles that I have read are related to transactions and queries and therefore locks related to data pages/tables but not many related to memory/threads/buffer resources.

I ran PerfMon with all counters that seemed relevant (Network related, Available threads, Available Work Items, Log Buffer Waits as well as the usual ones such as Processor & memory related) but there was nothing immediately obvious.

Any help would be appreciated as nobody seems to know how to resolve this problem.

Cheers

Preet

06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7862208 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process5caf63e8 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process55a38118 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process4e95a2f8 06/21/2010 08:17:30,spid5s,Unknown,waiter-list 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8 06/21/2010 08:17:30,spid5s,Unknown,owner event=e_waitNone type=producer id=process78622f8 06/21/2010 08:17:30,spid5s,Unknown,owner-list 06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152d80 nodeId=76 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process5caf64d8 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process4e95a3e8 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process55a38208 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process78622f8 06/21/2010 08:17:30,spid5s,Unknown,waiter-list 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8 06/21/2010 08:17:30,spid5s,Unknown,owner event=e_waitNone type=producer id=process55a382f8 06/21/2010 08:17:30,spid5s,Unknown,owner-list 06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152b80 nodeId=79 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process4e95a4d8 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process78623e8 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process5caf65c8 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process55a382f8 06/21/2010 08:17:30,spid5s,Unknown,waiter-list 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8 06/21/2010 08:17:30,spid5s,Unknown,owner event=e_waitNone type=producer id=process78624d8 06/21/2010 08:17:30,spid5s,Unknown,owner-list 06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152980 nodeId=81 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process5caf6208 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7ffe28 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7ebd38 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process4e95a118 06/21/2010 08:17:30,spid5s,Unknown,waiter-list 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8 06/21/2010 08:17:30,spid5s,Unknown,owner event=e_waitNone type=producer id=process55a38118 06/21/2010 08:17:30,spid5s,Unknown,owner-list 06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152380 nodeId=72 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7fea78 06/21/2010 08:17:30,spid5s,Unknown,waiter-list 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8 06/21/2010 08:17:30,spid5s,Unknown,owner event=e_waitNone type=producer id=processd75d38 06/21/2010 08:17:30,spid5s,Unknown,owner-list 06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152780 nodeId=2 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7ebf18 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7f9f18 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7fed48 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=processd75d38 06/21/2010 08:17:30,spid5s,Unknown,waiter-list 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8 06/21/2010 08:17:30,spid5s,Unknown,owner event=e_waitNone type=producer id=processd74e38 06/21/2010 08:17:30,spid5s,Unknown,owner-list 06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152200 nodeId=59 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=processd74e38 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7f9d38 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7ff2e8 06/21/2010 08:17:30,spid5s,Unknown,waiter-list 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8 06/21/2010 08:17:30,spid5s,Unknown,owner event=e_waitNone type=producer id=processd754c8 06/21/2010 08:17:30,spid5s,Unknown,owner-list 06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152e00 nodeId=63 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7ffa68 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=processd754c8 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7f8898 06/21/2010 08:17:30,spid5s,Unknown,waiter-list 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8 06/21/2010 08:17:30,spid5s,Unknown,owner event=e_waitNone type=producer id=process5caf6208 06/21/2010 08:17:30,spid5s,Unknown,owner-list 06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152100 nodeId=68 06/21/2010 08:17:30,spid5s,Unknown,waiter id=process55a384d8 06/21/2010 08:17:30,spid5s,Unknown,waiter-list 06/21/2010 08:17:30,spid5s,Unknown,owner id=process7fea78 06/21/2010 08:17:30,spid5s,Unknown,owner id=process7fed48 06/21/2010 08:17:30,spid5s,Unknown,owner id=process7ff2e8 06/21/2010 08:17:30,spid5s,Unknown,owner id=process7ffa68 06/21/2010 08:17:30,spid5s,Unknown,owner id=process7ffe28 06/21/2010 08:17:30,spid5s,Unknown,owner id=process55a38118 06/21/2010 08:17:30,spid5s,Unknown,owner id=process55a38208 06/21/2010 08:17:30,spid5s,Unknown,owner id=process55a382f8 06/21/2010 08:17:30,spid5s,Unknown,owner id=process55a383e8 06/21/2010 08:17:30,spid5s,Unknown,owner-list 06/21/2010 08:17:30,spid5s,Unknown,threadpool id=schedulerce2040 06/21/2010 08:17:30,spid5s,Unknown,waiter id=process4e95a6b8 06/21/2010 08:17:30,spid5s,Unknown,waiter-list 06/21/2010 08:17:30,spid5s,Unknown,owner id=processd74e38 06/21/2010 08:17:30,spid5s,Unknown,owner id=processd754c8 06/21/2010 08:17:30,spid5s,Unknown,owner id=processd75d38 06/21/2010 08:17:30,spid5s,Unknown,owner id=process4e95a118 06/21/2010 08:17:30,spid5s,Unknown,owner id=process4e95a2f8 06/21/2010 08:17:30,spid5s,Unknown,owner id=process4e95a3e8 06/21/2010 08:17:30,spid5s,Unknown,owner id=process4e95a4d8 06/21/2010 08:17:30,spid5s,Unknown,owner id=process4e95a5c8 06/21/2010 08:17:30,spid5s,Unknown,owner-list 06/21/2010 08:17:30,spid5s,Unknown,threadpool id=schedulerd78040 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process4e95a5c8 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process55a383e8 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process78624d8 06/21/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process5caf66b8 06/21/2010 08:17:30,spid5s,Unknown,waiter-list 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process4e95a6b8 06/21/2010 08:17:30,spid5s,Unknown,owner event=pending id=process5caf67a8 06/21/2010 08:17:30,spid5s,Unknown,owner-list 06/21/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152f00 nodeId=84 06/21/2010 08:17:30,spid5s,Unknown,waiter id=process5caf67a8 06/21/2010 08:17:30,spid5s,Unknown,waiter-list 06/21/2010 08:17:30,spid5s,Unknown,owner id=process7f8898 06/21/2010 08:17:30,spid5s,Unknown,owner id=process7f9d38 06/21/2010 08:17:30,spid5s,Unknown,owner id=process7f9f18 06/21/2010 08:17:30,spid5s,Unknown,owner id=process5caf6208 06/21/2010 08:17:30,spid5s,Unknown,owner id=process5caf63e8 06/21/2010 08:17:30,spid5s,Unknown,owner id=process5caf64d8 06/21/2010 08:17:30,spid5s,Unknown,owner id=process5caf65c8 06/21/2010 08:17:30,spid5s,Unknown,owner id=process5caf66b8 06/21/2010 08:17:30,spid5s,Unknown,owner-list 06/21/2010 08:17:30,spid5s,Unknown,threadpool id=schedulerbd0040 06/21/2010 08:17:30,spid5s,Unknown,resource-list 06/21/2010 08:17:30,spid5s,Unknown,process id=process5caf67a8 schedulerid=2 kpid=0 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process5caf66b8 taskpriority=0 logused=20084 waittime=15 schedulerid=2 kpid=5544 status=suspended spid=52 sbid=0 ecid=30 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process5caf65c8 taskpriority=0 logused=20081 waittime=31 schedulerid=2 kpid=6576 status=suspended spid=52 sbid=0 ecid=25 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process5caf64d8 taskpriority=0 logused=20079 waittime=31 schedulerid=2 kpid=4752 status=suspended spid=52 sbid=0 ecid=21 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process5caf63e8 taskpriority=0 logused=20076 waittime=31 schedulerid=2 kpid=7312 status=suspended spid=52 sbid=0 ecid=19 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process5caf6208 taskpriority=0 logused=20072 waittime=31 schedulerid=2 kpid=1740 status=suspended spid=52 sbid=0 ecid=13 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,process id=process55a384d8 schedulerid=3 kpid=0 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process55a383e8 taskpriority=0 logused=20084 schedulerid=3 kpid=6868 status=suspended spid=52 sbid=0 ecid=31 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process55a382f8 taskpriority=0 logused=20081 waittime=31 schedulerid=3 kpid=6696 status=suspended spid=52 sbid=0 ecid=26 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process55a38208 taskpriority=0 logused=20079 waittime=31 schedulerid=3 kpid=7180 status=suspended spid=52 sbid=0 ecid=23 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process55a38118 taskpriority=0 logused=20076 waittime=31 schedulerid=3 kpid=2640 status=suspended spid=52 sbid=0 ecid=18 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,process id=process4e95a6b8 schedulerid=4 kpid=0 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process4e95a5c8 taskpriority=0 logused=20084 waittime=15 schedulerid=4 kpid=2508 status=suspended spid=52 sbid=0 ecid=32 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process4e95a4d8 taskpriority=0 logused=20081 waittime=31 schedulerid=4 kpid=6328 status=suspended spid=52 sbid=0 ecid=27 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown

06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process7f9f18 taskpriority=0 logused=20059 waittime=78 schedulerid=2 kpid=6284 status=suspended spid=52 sbid=0 ecid=13 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process7f9d38 taskpriority=0 logused=20063 waittime=62 schedulerid=2 kpid=4760 status=suspended spid=52 sbid=0 ecid=7 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process7f8898 taskpriority=0 logused=20068 waittime=46 schedulerid=2 kpid=6772 status=suspended spid=52 sbid=0 ecid=12 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process7ebf18 taskpriority=0 logused=20059 waittime=78 schedulerid=1 kpid=5052 status=suspended spid=52 sbid=0 ecid=14 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process7ebd38 taskpriority=0 logused=20072 waittime=31 schedulerid=1 kpid=6208 status=suspended spid=52 sbid=0 ecid=15 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,process-list 06/21/2010 08:17:30,spid5s,Unknown,deadlock victim=process7fea78 06/21/2010 08:17:30,spid5s,Unknown,deadlock-list

Not sure really as it fails in different places. I thought this was probably related to a configuration setting somewhere or hardware bottleneck as it complains about thread or communication buffer resources. If I knew exactly what it was on about (network adapters, internal structures?) that would be a start.

Preet 2010-06-30 14:12:29

If deadlock happends, it means that 2 SQL server processes try to access the same table, index etc. So first of all you should discover these 2 processes. I believe that my questions in first post could help to find a problem

2010-06-30 18:50:21

Well that's what I thought Trace Flag 1222 would provide. As you can see it does not. Finding the processes running at the time of the deadlock may be a red herring as the data load fails in different places and if it due to a resource bottleneck then this may not be captured. The only thing I can do, I guess, is to run SQL Profiler each night with various events and hope some useful info is captured.

Preet 2010-07-02 10:43:45

ansaurus

tags:

views:

answers:

SQL Server Deadlock Problem - How to Resolve ?

related questions